Wednesday, November 19, 2008

Oracle - Today's Date

You probably know that sysdate returns today's date in SQL.

But what if you have a table with a field that records the time at which the record was added and you wish to find all the records that were added today (as an example lets call this field UpdateDt).

You first might try something

select * from myTable where UpdateDt >= Sysdate;

This will obviously not return any results because sysdate does not just include the current date but also the current time and obviously there are no records that were updated before the current date-time.

Next would be to try something like the following:
select * from myTable where UpdateDt >= sysdate - 1 day;

But the above statement depending on when the query was run could return not just records that were added today but also some of the records that were added yesterday (because sysdate - 1 day returns a date that is 24 hours in the past).

Unfortunately Oracle doesn't seem to have an easy function to return today's date with a time stamp of 12:00 am. So here is what you need to do:

select * from myTable where UpdateDt >= to_date(to_char(current_date),'dd-mon-yy');

where "to_date(to_char(current_date),'dd-mon-yy')" will return today's date with a time stamp of 12:00 am.

1 comment:

Ti@go said...

Actually, you have a simple function:

trunc (date, [ format ])
http://www.techonthenet.com/oracle/functions/trunc_date.php

If you don't pass the optional parameter (format), it does what you want.

Greetings,
Santiago Reil.