CODEWITHSUNDEEP
oracle-databasedatedatetime
d2907
d2907

Reputation: 902

Setting a date and hour in Oracle

am trying to get ALL data from an oracle table. I tried with this sentences in the WHERE clause.

date between to_date(sysdate-1) and to_date(sysdate);
date between (current_date-1) and (current_date);
date BETWEEN (TO_CHAR(SYSDATE-1,'DD-MON-YY')) AND (TO_CHAR(SYSDATE,'DD-MON-YY'));

Just to compare the results, I executed the query with this code:

date between to_date('08/04/2015 00:00:00.00', 'dd/mm/yyyy hh24:mi:ss.ff' ) and
to_date('09/04/2015 00:00:00.00', 'dd/mm/yyyy hh24:mi:ss.ff' )

I am totally sure this query gives me the right set of data that I am looking for, but this result is totally different to the ones that I've got with the first two examples and even the four results are different between them.

This query is executed automatically after midnight and I can update the date manually, so my question is how can I get the same result that I've got when I specified directly that I needed ALL the registers from the previous day (from 00:00 am to 12:00 pm).

Regards

Upvotes: 0

Views: 611

Answers (1)

Boneist
Boneist

Reputation: 23578

Doing TO_DATE() on something that's already a DATE datatype is a big no-no. You're relying on implicit conversions from a DATE to a string and then back to a DATE, which might cause unintended results. Far better to use something that explicitly does what you want it to do, eg. TRUNC().

Also, BETWEEN is inclusive - if you're after everything that happened yesterday including today at midnight, then you're fine to use BETWEEN.

If you want everything that happened yesterday excluding midnight today, then you'd be best off doing something like:

...
and date_col >= trunc(sysdate -1)
and date_col < trunc(sysdate)
...

Don't forget that DATE includes a time value, so if you're wanting everything across a single day, you have to remember to truncate the dates to midnight (e.g. trunc(sysdate))

Upvotes: 5

Related Questions