12 Replies Latest reply: Dec 13, 2012 3:31 AM by Sudhir_Meru RSS

    Filter Data based on date Condition

    Sudhir_Meru
      Hi,

      Below is the query which is performing cast operation to convert string to date. Now my requirement is to filter all the dates which is from 1/1/2010 or later.

      >> SELECT
      >> CAST( TICKETID AS VARCHAR2(4000)) TICKETID,
      CAST(CUSTOMERID AS VARCHAR2(4000)) CUSTOMERID,
      CAST(CUSTOMEREMAIL AS VARCHAR2(4000)) CUSTOMEREMAIL,
      CAST(CREATEDBY AS VARCHAR2(4000)) CREATEDBY,
      TO_CHAR (TO_DATE ( CAST (DATECREATED AS VARCHAR2 (4000)) , 'Mon DD YYYY HH:MIAM'),'DD-MM-YYYY HH24:MI:SS') DATECREATED
      FROM TICKETS
      I tried with the below format, But it shows as not a valid month to filter please suggest me how to perform the filter on this cast operation of cast conversion.


      >> SELECT * FROM
      >> ( SELECT
      CAST( TICKETID AS VARCHAR2(4000)) TICKETID,
      CAST(CUSTOMERID AS VARCHAR2(4000)) CUSTOMERID,
      CAST(CUSTOMEREMAIL AS VARCHAR2(4000)) CUSTOMEREMAIL,
      CAST(CREATEDBY AS VARCHAR2(4000)) CREATEDBY,
      TO_CHAR (TO_DATE ( CAST (DATECREATED AS VARCHAR2 (4000)) , 'Mon DD YYYY HH:MIAM'),'DD-MM-YYYY HH24:MI:SS') DATECREATED
      FROM TICKETS )
      WHERE
      DATECREATED >= TO_DATE('01-01-2010','DD-MM-YYYY HH24:MI:SS')
      Please suggest me how to put a filter on this date.

      Thanks
      Sudhir