CODEWITHSUNDEEP
oracle-database
Sachin Vaidya
Sachin Vaidya

Reputation: 125

Comparing date and timestamp with BETWEEN clause

The basic structure of my query is this:

SELECT....FROM....
WHERE SYSTIMESTAMP BETWEEN DateA and DateB.

I am trying various ways, one of them is, but getting errors like ORA-01861: literal does not match format string OR in some cases 'not a valid month'

WHERE
SYSTIMESTAMP BETWEEN 
      TO_TIMESTAMP(TO_DATE(DateA,'YYYY-MM-DD HH24:MI:SS'),  'DD/MM/YYYY HH24:MI:SSFF') AND 
      TO_TIMESTAMP(TO_DATE(DateB,'YYYY-MM-DD HH24:MI:SS'),  'DD/MM/YYYY HH24:MI:SSFF')

DateA and DateB columns are of type DATE and SYSTIMESTAMP is obviously TIMESTAMP.

NLS settings on my SQL developer are

Please help me in identifying the issue.

Upvotes: 0

Views: 6324

Answers (3)

Boneist
Boneist

Reputation: 23578

TO_TIMESTAMP and TO_DATE both take a string and convert it to a timestamp/date respectively using the specified format mask.

Therefore doing TO_TIMESTAMP(TO_DATE(..)...) doesn't make sense - especially when your original column is already in a date format! You need one or the other.

By doing TO_TIMESTAMP(TO_DATE(<date_column>..)...), what you're doing is forcing oracle to do some implicit conversions from a date into a string and back to a date then back to a string, so that it can be converted back into a timestamp. That looks something like this:

TO_TIMESTAMP(TO_CHAR(TO_DATE(TO_CHAR(<date_column>, <nls_date_format>), <your_date_format>), <nls_date_format>), <your_timestamp_format>).

That's massively overcomplicated, right? Plus, if one of those formats doesn't match, then you'll get the errors you're experiencing.

If you want to convert a DATE into a TIMESTAMP, you can use CAST, e.g.:

where systimestamp between cast(datea as timestamp) and cast(dateb as timestamp)

or you can let Oracle handle the implicit conversion from the date into a timestamp and just do:

where systimestamp between datea and dateb

or simply avoid the issue altogether by using sysdate instead of systimestamp, i.e.:

where sysdate between datea and dateb

Upvotes: 1

SandPiper
SandPiper

Reputation: 2906

You don't need to convert to a timestamp if you already have it in the DATE format. Just use:

WHERE SYSTIMESTAMP BETWEEN DateA AND DateB

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You do not need a conversion to check a timestamp against a date; for example:

SQL> desc testDate
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATEA                                              DATE
 DATEB                                              DATE

SQL> select * from testDate;

DATEA     DATEB
--------- ---------
21-MAY-17 23-MAY-17
19-MAY-17 21-MAY-17

SQL> select *
  2  from testDate
  3  where systimestamp between dateA and dateB ;

DATEA     DATEB
--------- ---------
21-MAY-17 23-MAY-17

SQL>

Upvotes: 0

Related Questions