0

Soo... I have a web app i'm working on. Here's the basics. (ASP.Net, C#, EF, Linq)

We have Projects which have and open and close date.

So imagine a table called projects with those fields. On that table we want a computed column for Time Open and Adjusted time open where Adjusted Time Open takes into account hours of operation.

right now you can set hours of operation from Sunday - Saturday or select that the day is not worked.

This is stored in a table and the values are stored as minutes from midnight, in UTC.

Everything in our db as far as dates is stored in utc.

So in the user defined function that calculates time open and adjusted time open (which are written and work, except for situations with daylight savings time.

For example:

We create a project where we're ahead one hour at 9am our local time. so that get's stored as 22:00:00 UTC. If we create a project when we're back one our at 9am our local time, that get's stored as 21:00:00 UTC. (I'm kinda guessing at the exact times, but you get the point).

This is fine, I have no problems with this. The problem comes in with hours of operation.

We basically when they set it, get the utc time and then convert that to minutes from midnight and store that in the table. So technically, that could be 22 * 60 or 21 * 60 depending on which part of daylight savings time we're in. So the calculations could be off.

Is this making sense? I have a hard time grasping and explaining some of this stuff.

Steph
  • 409
  • 2
  • 8
  • 14
  • http://stackoverflow.com/questions/180638/determine-time-zone-offset-in-t-sql – rkw Jul 14 '11 at 23:37
  • I'd like to avoid having to put tables in for timezones if at all possible. Is there anyway to do that? I would only need to convert from the webserver's timezone (which is stored in a table in the db) to UTC. – Steph Jul 15 '11 at 13:42

0 Answers0