Over a million developers have joined DZone.

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

I’m seeing people do this all the time. They want to hammer a date or timestamp constant into their SQL query, and they use the wrong function.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Couchbase.

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.

Are you a developer? Don’t miss the monthly Couchbase Developer Community Newsletter, covering the latest headlines on NoSQL and much more. Brought to you in partnership with Couchbase.

Topics:
date formatting ,format sql ,sql server. jooq ,date ,oracle ,sql ,timestamp ,database

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}