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, … Continue reading Don’t Format Dates in SQL. Use the DATE Literal!
(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types) There was an interesting discussion recently on the jOOQ mailing list about jOOQ's current lack of out-of-the-box support for TIMESTAMP WITH TIME ZONE data types. No one said that date, time and timezones are easy! There's an amusing piece … Continue reading What’s Even Harder Than Dates and Timezones? Dates and Timezones in SQL / JDBC!
Oracle database has its ways. In my SQL talks at conferences, I love to confuse people with the following Oracle facts: ... and the answer is, of course: Isn't it horrible to make empty string the same thing as NULL? Please, Oracle... The only actually reasonable slide to follow the previous two is this one: … Continue reading Are You Binding Your Oracle DATEs Correctly? I Bet You Aren’t
You're very likely to have gotten SQL date time arithmetic wrong. And why is that? Google it! You'll quickly find blog posts like these: Date / Time Arithmetic with Oracle 9 / 10 How does one add a day/hour/minute/second to a date value? Orace Date Arithmetic Tips And they're all advocating stuff like this: SYSDATE … Continue reading You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!