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!
Tag: Date
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
We all know that Oracle's DATE is not really a date as in the SQL standard, or as in all the other databases, or as in java.sql.Date. Oracle's DATE type is really a TIMESTAMP(0), i.e. a timestamp with a fractional second precision of zero. Most legacy databases actually use DATE precisely for that, to store … 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!