Date function on oracle

Question

I've got a question about date function on oracle.

I have the following table

statistic_table(
   pages AS varchar(10),
   date_created AS date
);

I have the following sql

SELECT COUNT(*) FROM statistic_table WHERE date_created BETWEEN sysdate-5 AND sysdate-1
and
SELECT COUNT(*) FROM statistic_table WHERE date_created BETWEEN to_date('12-AUG-2011') AND to_date('16-AUG-2011');

the question is, why is it return different numbers. assuming sysdate-5 returns 12-aug-2011 and sysdate-1 returns 16-aug-2011

Any help would be much appreciated!

Cheers,

StevieG · Answer 1 · 2011-08-17T13:58:26.513

sysdate - 5 will give you a date with the current time. So if I ran it at 1pm precisely, the query would be equivalent to:

select (*) 
FROM statistic_table 
WHERE date_created BETWEEN to_date('12-Aug-2011 13:00:00') 
                       AND to_date('16-Aug-2011 13:00:00')

whereas the second query is:

select (*) 
FROM statistic_table 
WHERE date_created BETWEEN to_date('12-Aug-2011 00:00:00') 
                       AND to_date('16-Aug-2011 00:00:00')

you should probably try this instead:

select (*) 
FROM statistic_table 
WHERE date_created BETWEEN trunc(sysdate) -5 
                       AND trunc(sysdate) -1

score 3 · Accepted Answer · answered Aug 17 '11 at 13:52

3

A date in Oracle is a point in time with a precision of a second.

SYSDATE returns the current date and time and is therefore not the same as to_date('17-AUG-2011'):

SQL> SELECT to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss') FROM DUAL;

TO_CHAR(SYSDATE,'DD-MON-YYYYHH
------------------------------
17-aug-2011 15:52:13

Use the TRUNC function if you only want the date component:

SQL> SELECT to_char(trunc(sysdate), 'dd-mon-yyyy hh24:mi:ss') FROM DUAL;

TO_CHAR(TRUNC(SYSDATE),'DD-MON
------------------------------
17-aou-2011 00:00:00

answered Aug 17 '11 at 13:52

Vincent Malgrat

66,725
9
119
171

if I want to use sysdate, how do I make them to return the same number of rows? should I cast it? if so, how? – gumpi Aug 17 '11 at 13:54
@gumpi: you can use the `trunc` function on `sysdate` – Vincent Malgrat Aug 17 '11 at 13:56

score 2 · Answer 3 · answered Aug 17 '11 at 13:52

Because SYSDATE includes a time component, so if the current time is 11:22:33, then

SELECT COUNT(*) FROM statistic_table 
WHERE date_created BETWEEN sysdate-5 AND sysdate-1

is actually equivalent to

SELECT COUNT(*) FROM statistic_table
WHERE date_created BETWEEN to_date('12-AUG-2011 11:22:33','DD-MON-YYYY HH24:MI:SS')
                       AND to_date('16-AUG-2011 11:22:33','DD-MON-YYYY HH24:MI:SS')

To avoid the time component do this:

SELECT COUNT(*) FROM statistic_table 
WHERE date_created BETWEEN TRUNC(sysdate)-5 AND TRUNC(sysdate)-1

score 1 · Answer 4 · answered Aug 17 '11 at 13:53

An Oracle DATE always has a day and a time component.

sysdate-5 returns a date exactly 5 days ago. If today is August 17 at 10 AM, for example, sysdate-5 returns August 12 at 10 AM.

to_date('12-AUG-2011', 'DD-MON-YYYY'), on the other hand, returns August 12 at midnight. So it returns a date that is 10 hours earlier than sysdate-5.

score 0 · Answer 5 · answered Aug 17 '11 at 13:56

sysdate auto returns with a time component as mentioned by the previous answers.

When using to_date it is converting a string to a date. With this being said you can pass in parameters to make it return the same thing.

Have a look at this link that explains it.

to_date parameters

Date function on oracle

5 Answers5