Is a date in Oracle-SQL without a time-value always 00:00:00?

Question

Heyho,

I need to grab some datas from actions which been done from date A 00:00:00 and date B 00:00:00 (in this case Date A: 16.07.2010 Date B: 20.07.2010) so i wrote this select-statement:

Select avg(cnt),fext from (
        Select 
            to_char(mytable.dateadded, 'DD.MM.YYYY') dateadded,
            fext, 
            count(id) cnt 
        from mytable 
        where dateadded between
            to_date('16.07.2010', 'dd,MM,YYYY') and 
            to_date('20.07.2010', 'dd,MM,YYYY')
        group by 
            to_char(mytable.dateadded, 'DD.MM.YYYY'),
            fext)
group by fext;

The original (and working) statement had:

    to_date('16.07.2010 00:00:00', 'dd,MM,YYYY HH24:Mi:SS') and 
    to_date('20.07.2010 00:00:00', 'dd,MM,YYYY HH24:Mi:SS')

so the question is: does the

    to_date('16.07.2010', 'dd,MM,YYYY') and 
    to_date('20.07.2010', 'dd,MM,YYYY')

already set the time to date A and B to 00:00:00?

Greetz

Answer 1

If you does not specify time part of date it will be 00:00:00.

If you worry about time part you always can truncate time part:

Trunc(to_date('16.07.2010', 'dd.MM.YYYY')) and 
Trunc(to_date('20.07.2010', 'dd.MM.YYYY'))

Answer 2

The easiest way is to use ANSI date literals, which doesn't allow time to be specified and is thus with a 00:00:00 time part internally:

dateadded between date '2010-07-16' and date '2010-07-20'

Your expressions look odd, as your date string has dots as the time component separator, but your date format has commas. Oracle accepts it though:

rwijk@XE> select to_date('16.07.2010', 'dd,MM,YYYY') from dual;

TO_DATE('16.07.2010
-------------------
16-07-2010 00:00:00

1 row selected.

I'd use the ANSI date literal for its simplicity.

Regards, Rob.

Answer 3

this query will returns you one row which answers your question i think :

SELECT TO_DATE('16.07.2010 00:00:00', 'dd,MM,YYYY HH24:Mi:SS'),  
       TO_DATE('16.07.2010', 'dd,MM,YYYY')
  FROM dual
 WHERE to_date('16.07.2010 00:00:00', 'dd,MM,YYYY HH24:Mi:SS') = to_date('16.07.2010', 'dd,MM,YYYY')  

Answer 4

This is one thing that SQL is consistent about - if you don't provide a time portion on a DATE data type that includes a time portion (IE Oracle, PostgreSQL while MySQL and SQL Server explicitly call it DATETIME), then the time portion is defaulting to exactly midnight of that date - 00:00:00. And being midnight, that means the start of the date - if you want to include the entire date in a date comparison, the value's time portion needs to be 23:49:49 -- a second to midnight.