UTC Dates

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  October 30, 2003 at 6:05 pm

  #480383

  You are more than welcome.

  Any change of populating the table

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  October 31, 2003 at 5:26 am

  #480426

  quote:

  ---

  Sorry that I'm coming into this a little late, but why don't you store the dates in UTC format. Then create a table that has the timezone definitions like below (no guarantee on syntax)

  CREATE TABLE TimeZone

  (

  TimeZoneId INT,

  TimeZoneName NVARCHAR(25),

  TimeZoneOffset INT,

  Year INT,

  DaylightSavingsStart DATETIME,

  DaylightSavingsEnd DATETIME

  )

  You'd have to then create two UDFs.

  The first would be used to take any date and timezone, would extract the year, look to see if the date fell within the Start/End dates for the daylight savings, then do a DATEADD to get the UTC datetime.

  The second would take a UTC date and timezone, would extract the year, look to see if the date fell within the Start/End dates for the daylight savings period, then do a DATEADD to get the local datetime.

  ---

  Well, yes, that's the way to do this on the server, but the two sticking points for me are getting the client's time zone and finding the values to populate this table. I'm also not sure I'd want to handle what is basically a presentation issue by passing every date in the select list through a UDF.

  --Jonathan

  
  
  --Jonathan