Converting/Searching GETUTCDATE()

Question

If I store all my dates in SQL Server 2005 as GetUtcDate() what is the best way to search on these date fields. I'm using C# so should i convert the date submitted by the user to UTC using C# (passing to Stored Proc) to search my dates in SQL?

Also, since i'm using UTC do I need to worry about day light savings? When I want to display the date/time to the user/view do I simply just add/subtract the offset? Is there anything to look out for when converting my UTC date/time from the database to the user timezone for display?

Answer 1

"should i convert the date submitted by the user to UTC using C# (passing to Stored Proc) to search my dates in SQL?" - Sounds like a good idea

"since i'm using UTC do I need to worry about day light savings?" - no, C# will take care of that when you're converting between local and UTC time.

Don't just add/subtract the offset, use the C# DateTime functions to convert between UTC and local time. That way it'll take care of DST.

Answer 2

Dealing with time zones is a major pain in the @$$. One thing to consider is that Windows only stores the current DST rules, not historic rules. So if you are relying on the rules to be able to accurately recreate the old values, you might find some discrepancies in your data. DST rules change all the time. Some countries don't even have set rules, they just announce the dates every year.

If you cannot afford discrepancies in your data, you might be better off storing the date as a string with the time zone information encoded in it. In .Net you can use DateTime.ToString("O"). This format is culture agnostic so you will always get the same format no matter what culture the code is running in.

var origDt = DateTime.Now;
var dtStr = origDt.ToString("O");
var newDt = DateTime.Parse(dtStr, null, System.Globalization.DateTimeStyles.RoundtripKind);
Console.WriteLine(dtStr);
if (newDt == origDt)
    Console.WriteLine("Dates equal"); // should be true
else
    Console.WriteLine("Dates not equal");

Check out the MSDN documentation for more information on this format style.

Of course this comes at a cost. It will be inefficient to search the database by date (it can be done, but the strings need to be converted to dates). Chances are the time zone differences won't matter too much anyway. It really depends on what you are doing with the data and how important accuracy is.

You might want to make sure that the project actually requires UTC and time zones before you go down this path. There is a decent chance that just storing the time from the local computer and ignoring time zones is good enough.