Friday 8 April 2011

Never mind World Peace... how about a standard data type and format for dates?

It's an utterly lost cause as the code to handle dates will be buried deep inside the database engines. Every major application seems to handle dates its own way. This makes moving the damn things around a real pain.

SAS: dates are integers. Day Zero = 1/1/1960. Date arithmetic is simple, but date functions sparse. Date formats are pre-historic.

Oracle: dates are… really complicated, but to_date(date_string, format) and to_char(date, format) are really powerful. Date arithmetic is simple and there are some useful date functions. Date formats are whatever you want them to be.

Teradata: dates are (year-1900)+month*100+day. Date functions are few and date formats are… there’s one. Date arithmetic is simple if you don’t try to be clever.

Excel: dates are integers. Day Zero = 1/1/1900. Date arithmetic is simple, and format(date, formatstring) in VBA is really powerful.

ANSI SQL supports add_months(date, +/- n) which is reasonably useful.

None of them handle week numbering (YYYYWW) consistently well at the turn of the year. This year gets some really odd things in Oracle.

I'm guessing other databases have their own quirks.

No comments:

Post a Comment