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 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!

How to Extract a Date Part in SQL

The Modern SQL Twitter account (by Markus Winand) published a hint about how to extract a date part in SQL: https://twitter.com/ModernSQL/status/570294338124697600 Is it true? Yes it is, in the SQL standard and in a variety of standards-compliant databases. But let's check what jOOQ does when you run the following program on all 18 currently supported … Continue reading How to Extract a Date Part in SQL

Are You Binding Your Oracle DATEs Correctly? I Bet You Aren’t

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!

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!