3

I transfer data from one SQL Server to another using SELECT * INTO .... statement (in Management Studio). Both databases have different timezone settings.

Is it possible to adjust all date/time fields to different timezone automatically? Maybe some query parameters or connection settings?

John Conde
  • 217,595
  • 99
  • 455
  • 496
user1066007
  • 31
  • 2
  • Which **version** of SQL Server?? With **SQL Server 2008**, you get the `DATETIMEOFFSET` datatype, and the `SWITCHOFFSET` function to convert date&time from one timezone to another. – marc_s Nov 25 '11 at 17:45
  • SQL Server 2008. Generally I know how to convert one particular field. What I want is to be able to use SELECT * INTO .... I would like to avoid converting all fields, one by one. – user1066007 Nov 25 '11 at 18:20

2 Answers2

1

Since you are on SQL Server 2008, you could use the built-in functionality of the DATETIMEOFFSET datatype, and the SWITCHOFFSET function.

You could do something like:

INSERT INTO dbo.YourTargetTable( ...... )
   SELECT
        SWITCHOFFSET(TODATETIMEOFFSET(YourSourceTime, '+04:00'), '-09:00'),
       .......

TODATETIMEOFFSET converts your "regular" date without any timezone information into a DATETIMEOFFSET type with a time zone (of your source location), and then you can apply a SWITCHOFFSET call to that value to "switch" your date/time to your target location's time zone.

This can be done in the scope of the INSERT....SELECT statement - no separate "row-by-row" updating necessary.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

I think this will be helpful, please follow the link:

Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

Shortly, you do need to build time zone and DST tables to get some help with time zones.

Thanks

Community
  • 1
  • 1
Elias Hossain
  • 4,410
  • 1
  • 19
  • 33