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

    Unanswered: timestamp conversion in sql

    Hi All,
    Field name is createdate and datatype is timestamp.
    data format in field : 14-APR-10 02.06.05.731000 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?

    Thanks
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Use a function which will exactly tell Oracle what you want. Such as
    Code:
    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
    Posts
    136
    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')
    Regards
    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
  •