Tag Info

Always store data in the database in UTC and then use the appropriate timezone to convert it during display, in the application. Is the only sensible approach when it comes to consider data mobility (geo-replication), user location and , most importantly, daylight savings. If you store local time there will be times when you cannot distinguish between column ...

The best way to convert a non-current UTC date into local time is to use the CLR. The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary... For one of the many examples, check out Harsh Chawla's blog post on the topic. Unfortunately, there is nothing built-in that can handle this type of conversion, ...

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC. By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may ...

I found this answer on StackOverflow that provides a User Defined Function that appears to accurately translate the datetimes The only thing you need to modify is the @offset variable at the top to set it to the Timezone offset of the SQL server running this function. In my case, our SQL server uses EST, which is GMT - 5 It's not perfect and probably won't ...

There are a couple of good answers to a similar question asked on Stack Overflow. I wound up using a T-SQL approach from the second answer by Bob Albright to clean up a mess caused by a data conversion consultant. It worked for almost all of our data, but then I later realized that his algorithm only works for dates as far back as April 5, 1987, and we had ...