CODEWITHSUNDEEP
sql-server
user2561997
user2561997

Reputation: 443

SQl Server Timezone offset Calculation

I am storing all my dates in SQL Server Datetime fields in UTC Date. There is a requirement where by I have to calculate local Datetime in a procedure from the UTC Date field, and for that i have the Time zone offset of the local datetime.

For ex. my Timezone offset is:'05:30:00'

and UTC Date is: 2013-02-09 08:34:12.037

Desired output: 2013-02-09 14:04:12.037

Now is there a simple way where of doing this without DateAdd and splitting the offset in hours and minutes.

Upvotes: 1

Views: 3346

Answers (2)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

You should be able to use the SWITCHOFFSET function. Here is an example:

declare @dt datetime;

set @dt = '2013-02-09 08:34:12.037';

select SWITCHOFFSET(CONVERT(datetimeoffset, @dt), '+05:30') as 'DATETIMEOFFSET',
       CAST(SWITCHOFFSET(CONVERT(datetimeoffset, @dt), '+05:30') as datetime) as 'DATETIME'

-- Outputs: 
--
-- 2013-02-09 14:04:12.0370000 +05:30       2013-02-09 14:04:12.037

Upvotes: 2

Paul
Paul

Reputation: 1066

Use the convert with 112:

declare @d datetime
set @d = getdate()
declare @s nvarchar(20)
set @s = convert(varchar, @d, 112)
print @s

That string will have the year, month, seconds etc.. always on the same position

Extract the desired part with substring:

print substring(@s, 1, 4) -- the year

Now recalculate the entire thing to minutes, by multiplying the hours by 60 and adding the minutes. Now substract your minutes delta from that number. Build a new string with the adjusted date-time, and convert that back to datetime. But... if you need to know the date as well, and you want to do it correct.... there is some coding left.

My advice: do use dateadd it's simple and correct (substract minutes is my advice).

Upvotes: 0

Related Questions