SQL Server 2008 will finally support real date/time data types. I never liked the “datetime” (timestamp) only model Microsoft’s offered for over a decade. To be honest, I’m not sure if SQL Server inherited DATETIME from Sybase or Visual Basic, or if VB got it from SQL Server, but it’s still wrong.
I want a date – why do I also have a time component?
I want a time – why is there a date component?
I want a timestamp with 1-millisecond accuracy – why does Microsoft use floating point to store the value, unable to provide better than 0.333 second granularity?
DB2 provides dates and times as well as timestamps. [One of the few things DB2 does notably better than SQL Server or Oracle.] Oracle, alas, still doesn’t get it; nice of them to add TIMESTAMP WITH LOCAL TIME ZONE (yes, that’s actually the data type name), but still lacking in more atomic data types like ‘time’ with no date component. Java’s also pretty brain damaged on the topic but Java7 will (finally) resolve that with JODA-cum-JSR310. Python nicely solved this some years back with the datetime package. Other languages have equally handled this well (though C++ still lacks an adequate standard solution; time_t is a bit archaic and simplistic by today’s standards).
Looks like SQL Server 2008′s going to finally solve this, and well. The datetime2 type smells like FILESYSTEMTIME (but with a 1-1-1 0:0:0 origin), and datetimeoffset shows they’re rounding out the solution and not going cheap (a true time-based delta/offset. Hooray). The new smalldatetime type is interesting; a timestamp of limited range (only 1900 thru 2079) and precision (1 minute granularity), but also physically smaller (4 bytes). For applications using a lot of ‘current’ data where seconds aren’t relevant (i.e. the overwhelming majority of application data), this should be quite popular.