0

I am looking for suggestion I have a few databases and deployed in Germany & UK Datacenters so all DateTime columns contain stores Datetime values according to their region.

Now my organization want to deploy Germany Database to UK Server to save licensing cost and I need to figure out the way that all applications keep on working as they are now.

The biggest issue is the DateTime columns, I need to find the optimum solution to change all DateTime columns data to UTC DateTime.

I believe my stored procedure will keep working fine as I am moving databases to UK region.

I need to update my C# code to display region specific date time when displaying data

Umer Farooqui
  • 208
  • 2
  • 7
  • 1
    What is the _precise_ column data type in the database for your values? – gunr2171 Aug 05 '22 at 17:09
  • See [Storing UTC Is Not a Silver Bullet - Jon Skeet](https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/). This is a must read for your situation just to make sure you develop the correct solution. – Igor Aug 05 '22 at 17:10
  • 1
    Does this answer your question? [SQL Server - Convert date field to UTC](https://stackoverflow.com/questions/2700197/sql-server-convert-date-field-to-utc) – gunr2171 Aug 05 '22 at 17:13
  • You'll have to carefully consider the intent of each datetime column. If they represent points in time, a UTC DateTime would be sufficient (though a UTC DateTimeOffset would be more explicit). If you're representing a birth date, or a calendar date, or an appointment time, additional metadata will be important. You might need to add a column to represent which time zone that date/time was meant to represent, and change your consuming code to take that into account. – StriplingWarrior Aug 05 '22 at 17:15
  • It's also impossible to accurately work this out in cases of DST differences, where you haven't stored the offset. For example, which offset is `2022-10-30 01:05:00`? – Charlieface Aug 05 '22 at 17:55
  • Actually, I am looking for something which works best for me as I have a C# code base to handle as well. I am also thinking to keep Germany DateTime data as it is and changing stored procedures and code to always save in the German time zone. So I am on the hunt for an optimum solution that works in a large codebase and multiple databases – Umer Farooqui Aug 05 '22 at 19:04

1 Answers1

0

Solution 1

You can use German locales on your British servers. If that's a problem with your app, then you can adjust the values in the app.

Solution 2

You can find all DateTime columns of all tables in your database

select schema_name(t.schema_id) + '.' + t.name as [table],
       c.column_id,
       c.name as column_name,
       type_name(user_type_id) as data_type,
       scale as second_scale
from sys.columns c
join sys.tables t
     on t.object_id = c.object_id
where type_name(user_type_id) in ('date', 'datetimeoffset', 
      'datetime2', 'smalldatetime', 'datetime', 'time')
order by [table],
         c.column_id;

(you can of course adjust the types if needed) and you can even generate a script for each column:

select 'update ' + schema_name(t.schema_id) + '.' + t.name + ' set ' + column_name + ' = dateadd(hour, -1, ' + column_name + ')'
from sys.columns c
join sys.tables t
     on t.object_id = c.object_id
where type_name(user_type_id) in ('date', 'datetimeoffset', 
      'datetime2', 'smalldatetime', 'datetime', 'time')
order by [table],
         c.column_id;

and then run the scripts generated this way. Note that this is untested, so you should test this very well before you apply this on your actual database.

Solution 3

Always subtract an hour from dates earlier than a given date. This is an admittedly messy solution and it should only be done if all else fails.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175