Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KJensen
Starting Member
12 Posts |
 Posted - 2009-03-12 : 19:51:01
|
| In a database of 31 tables I have 42 instances of datetime-columns with a default-value of GETDATE().Now it has been decided to store all dates as UTC in the database, and that also mean I will have to change GETDATE() to GETUTCDATE().Is there an easier way than to get cracking with the manual work? I need to retain the data, so simply dropping and re-creating the tables from a search-replaced script is not really an option. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-12 : 20:48:08
|
| What are you asking for help with, creating the new defaults, or updating the existing dates to UTC datetimes?Updating the data will be the hardest, because you will have to determine the UTC time at the time the data was inserted. For example, the current difference for Eastern US is 4 hours, but it was 5 hours before the change last Sunday morning at 0200. You will need to create a table with the start and stop datetime for each switch, along with the UTC offset.Also, the offset is undetermined for the period of time from 1 to 2 am on the day that the switch is made from DST back to standard time, because the system time reverts to 1 am when it reaches 2 am. That means a time of 0130 could have an offset of either 4 or 5 hours to UTC, depending on if the row was inserted before or after the time change.CODO ERGO SUM |
 |
|
|
KJensen
Starting Member
12 Posts |
Posted - 2009-03-13 : 14:25:48
|
| Oh, I see how this could be misunderstood.I only have to handle dates from now on forward as UTC. So I really only want to alter the table schema (not the data itself), so future default values will be getutcdate() - not getdate(). |
|
|
|
|
|
|
|
|
Convert all defaults GETDATE() to GETUTCDATE()