Don’t Format Dates in SQL. Use the DATE Literal!

Posted on by lukaseder
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 WHERE clause)
  • They’re much more readable than any other option
It’s the little things that sum up to make the big difference.

Published by lukaseder

I made jOOQ

One thought on “Don’t Format Dates in SQL. Use the DATE Literal!”

  1. Nice tip, simple and straightforward. I am used to parse String with TO_DATE function but now I will take this option into consideration, tks.

    Reply

Leave a Reply