Monday, February 23, 2009

SQL 2008 date and time values

I've been looking at SQL 2008 for well over a year in various guises, but between one thing and another it's only now that I'm actually doing any serious work with SQL 2008 databases. It's a chicken and egg situation, of course; you don't want to fool around with valuable customer database installations that are running perfectly well with SQL 2005 until you're completely confident with the new version, but you don't become completely comfortable with the new version until you've worked intensively with it for a while.

However, I was examining a problem involving frequent calculations on date values recently and SQL 2008 seemed like the obvious choice. Dates are painful in SQL 2005 and all earlier versions; a date and a time are always stored together even if you're only interested in the date or the time. You can ignore the other component, but you need to take care it doesn't come back and bite you when you're doing calculations: March 4 2009 is two days later than March 2 2009 but March 4 2009 01:12 is not two days later than March 2 2009 23:59, so the answer you get is very much dependent on how you frame the question.

There are ways around these problems, of course (I usually pass all date and time inputs through cleansing functions so they all save the same way), but it's all extra work for no good reason.

SQL 2008 though, among many other improvements, features separate date and time data types. This is a particular blessing, and works simply and beautifully. I'm also thrilled to at last have a debugger in the Management Studio, and working without it in SQL 2005 databases has rapidly become frustrating.

Labels: , ,