Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008

    Unanswered: timestamp conversion in sql

    Hi All,
    Field name is createdate and datatype is timestamp.
    data format in field : 14-APR-10 PM

    NLS parameter timestamp format is DD-MON-RR HH.MI.SSXFF AM (but dont want to set this parameter when execute the query eveytime)
    my input format in query is DD-MM-YY

    select * from emp where createdate between '03-07-10' and '07-07-10'

    but not getting desired output.

    how to get data between two dates?

    Pawan Kumar

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Use a function which will exactly tell Oracle what you want. Such as
    select * from emp
    where createdate between to_date('03-07-10', 'dd-mm-yy') and ...
    Because, Oracle is probably confused (and so am I). What is '03-07-10'? Is it July 3rd 2010? Is it October 7th 2003? Is it ...?

    Besides, '03-07-10' is just a string (CHARACTER!), not a date (although it might look as one to you). Therefore, NEVER rely on possible implicit datatype conversion. ALWAYS use TO_DATE when dealing with dates.

  3. #3
    Join Date
    Mar 2008
    I got it...

    select orderid from OMS_ORDADDITIONALDATA where KEYNAME='programId' and keyvalue='11976' and trunc(createdate) between to_date('13-07-10','dd-mm-yy') and to_date('14-07-10','dd-mm-yy')
    Pawan Kumar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts