I'm trying to run a query and want to verify the date criteria I'm using. Is TO_DATE('01/01/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') the same as TO_DATE('01/01/2016','MM/DD/YYYY')? This would be the start date I'm trying to verify. I'm using oracle sql in sql developer.
Thanks
In Oracle, a DATE is a binary data type consisting of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those components and it is NEVER stored in any particular human-readable format.
When you use TO_DATE and do not specify parts of the date then the defaults will be (as per the SQL Data-Types documentation):
The default date values are determined as follows:
- The year is the current year, as returned by
SYSDATE.- The month is the current month, as returned by
SYSDATE.- The day is
01(the first day of the month).- The hour, minute, and second are all
0.
If you use:
WITH dates (dt) AS (
SELECT TO_DATE('01/01/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2016','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT TO_DATE('2016-01','YYYY-MM') FROM DUAL UNION ALL
SELECT DATE '2016-01-01' FROM DUAL
)
SELECT dt,
DUMP(dt)
FROM dates
Then the output is identical for all rows:
| DT | DUMP(DT) |
|---|---|
| 2016-01-01 00:00:00 | Typ=12 Len=7: 120,116,1,1,1,1,1 |
| 2016-01-01 00:00:00 | Typ=12 Len=7: 120,116,1,1,1,1,1 |
| 2016-01-01 00:00:00 | Typ=12 Len=7: 120,116,1,1,1,1,1 |
| 2016-01-01 00:00:00 | Typ=12 Len=7: 120,116,1,1,1,1,1 |