ASP.NET Date/Time Conversions

Question

I think I am overthinking this issue a little and I'm hoping someone can help me out.

I have an ASP.NET application with a SQL Server back end. I am storing all my dates in UTC format and doing the appropriate conversions to the local time zone of the browser viewing it. One of the pages asks for a start date and end date (no times).

I am taking the start date and setting the time to 00:00:00 hours (midnight) and I'm taking the End time and adding a time of 23:59:59, so that the date range covers the whole day. Now what I'm trying to do is do a SQL query to do a search for records in this date range. The problem is, the data in SQL is in UTC time and the user is typing their dates and times in their local date and times.

My quickest solution was to convert the date and time to UTC, then search the records. However, by doing this, I am to believe ASP.NET converts the given time and date to UTC based on the server time zone.

How can I convert a date and time to UTC time based on the time zone of the user?

Answer 1

Here is a very similar question, also from stackoverflow.com.

Answer 2

When you take the user's input, make sure you use the DateTime constructor that takes a DateTimeKind argument. This way you can specify that their input is in UTC on creation of the object.

var userInputTime = new DateTime(year, month, day, 
                                 hour, minute, second, 
                                 DateKind.Utc);

Calling .ToUniversalTime() on this DateTime object will have no effect.

If instead you take their input and just create a DateTime using a simpler constructor, the DateTimeKind will by default be set to "Local", so when you do a UTC conversion, it actually will change it.

Answer 3

You want to look into the MySql function:

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() converts a datetime value dt from time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones may be specified as described in Section 5.10.8, “MySQL Server Time Zone Support”. ...

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

Answer 4

You're thinking is correct - the time conversions are based on the timezone of the server. Nothing passed in ServerVariables (for example) carries the user's local time.

That having been said, you have a couple of options:

1. If you do any sort of user profiling, you might store the user's UTC offset there, and recall it for your calculations. That's only as reliable as... well... your users.
2. On the input form your users are filling out, you could use Javascript (not IIS!) to create or populate a hidden INPUT field with the local date/time. This link might help you a little with that.

The only caveat with #2 is whether or not the user's workstation does a time sync and obtains the correct UTC offset.

Answer 5

If you are using .net 3.5 use TimeZoneInfo