I’m seeing people do this all the time. They want to hammer a date or timestamp constant into their SQL query, and the only function they know is the TO_DATE() or TO_TIMESTAMP() date parsing function:
SELECT TO_DATE ('20150801', 'yyyymmdd')
FROM DUAL;As observed in this Stack Overflow question, for instance:
TO_DATE ('20150801', 'yyyymmdd') AS DAY_20150801_TOTAL,
TO_DATE ('20150802', 'yyyymmdd') AS DAY_20150802_TOTAL,
TO_DATE ('20150803', 'yyyymmdd') AS DAY_20150803_TOTAL,
TO_DATE ('20150804', 'yyyymmdd') AS DAY_20150804_TOTAL,
TO_DATE ('20150805', 'yyyymmdd') AS DAY_20150805_TOTAL,
TO_DATE ('20150806', 'yyyymmdd') AS DAY_20150806_TOTAL,
TO_DATE ('20150807', 'yyyymmdd') AS DAY_20150807_TOTAL,
TO_DATE ('20150808', 'yyyymmdd') AS DAY_20150808_TOTAL,
TO_DATE ('20150809', 'yyyymmdd') AS DAY_20150809_TOTAL,
TO_DATE ('20150810', 'yyyymmdd') AS DAY_20150810_TOTAL,
TO_DATE ('20150811', 'yyyymmdd') AS DAY_20150811_TOTAL, [...]Date parsing is important only if your date input is really stored as strings in a table and in a custom format, and you want to manipulate the dates. If you are using date constants, please save some time and improve readability by using the DATE literal or TIMESTAMP literal.
Here’s an example:
SELECT DATE '2015-08-01' AS d, TIMESTAMP '2015-08-01 15:30:00' AS ts FROM DUAL;
Most major RDBMS support the above SQL standard literals. The date format that you should use with these is the ISO 8601 format. Advantages of these literals:
- They’re really constants and as such, they’re parsed only by the SQL parser, not the execution engine (e.g. when put in a
WHEREclause) - They’re much more readable than any other option
It’s the little things that sum up to make the big difference.
| Reference: | Don’t Format Dates in SQL. Use the DATE Literal! from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
