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;
, 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;
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.
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
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.