Thursday, January 04, 2007

GETUTCDate()

Pretty odd title I know. I was reading an article at sqlservercentral.com (registration required) about defaults and the ups and downs of using them. It was an opinion piece and in my experience there is no one right answer to that sort of thing. Document what you have done and why you have done it and keep the big picture in mind is about the best you can do when it comes to that sort of thing.

Buried in that same article was a little info about getutcdate() which as you might have guessed is a get date function that returns the GMT date and time. As I have mentioned before, anyone who has had the misfortune of spending any time discussing database design with me, will attest to my disdain for business logic contained within the database. Again, I stress, business logic is different from business rules, but that is another discussion. So what does the getdate() function have to do with business logic. Well, I would propose that getting the date and time via the server and allowing that to dictate format implies a certain amount of logic. Namely the timezone for which the time is going to be reported. The database doesn't care about the time zones and to try to manipulate it in the database would constitute some form of business logic. Having said that out loud, it sounds pretty silly and it is certainly nothing I would ever make a case out of, but why deal with that when you can return the GMT via the getutcdate() function. That way you always know what time you are going to pull from the database and adjust for it programagically.

At the company I work for we have stumbled into a time zone issue that hadn't been thought of. Because the getdate() function is used and thereby expected by the code, it is difficult to handle customers on either coast that connect to servers in the Midwest. By returning GMT time as opposed to local time, adjusting for time zones becomes more standard.

So nothing ground breaking, but something to think about the next time you are looking at the getdate() function. Maybe the getutcdate() function would serve you better in the long run.


Have a great day!
-mike

"We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true."
-Robert Wilensky

No comments: