datetime - UTC - datetime
-
I would like to convert datetime to UTC and UTC to datetime.
How is this possible...?
Any link where to find useful informations?
Thank in advance
-
Play around with the GETUTCDATE() function:
DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()
-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)
-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)
SELECT @LocalDate, @UTCDate, @LocalDate2
-
thank very mauch...
Besides I have found tables that have time expressed with 10 numbers:
how can convert to and vice-versa
Thank
-
number:
like:
1123156932
thank
-
Hi,
I played with the UTC part or your question a while back. The problem I found was that getutcdate() function really only worked for the here and now. If you gave it a date in the past, it didn't say to itself: "Hmmmm, what would the UTC time have been for that date taking into account things like Daylight Savings Time?" I working on a function to fit my situation which you might get some ideas from. About the time I was ready for my final testing, I was told someone upstream from me was already handling the problem so it never got implemented. Here is what I came up with. Your calculations may vary according to your timezone.
CREATE function dbo.fn_UtcToCentralTime(@DateToConvert Char(19))
-- 11-12-2004 Teague Byrd
-- Takes a UTC date stored as char(19) e.g. '2004-11-12 13:25:00' and converts it to Central time.
-- Daylight Saving time is taken into account in the calculation.
-- 11-17-2004 THIS IS NOT CURRENTLY USED. SEEMS TO WORK BUT NEEDS A BIT MORE TESTING
--
returns smalldatetime
as
begin
declare @YearPart char(4)
declare @FirstSundayInApril smalldatetime
declare @LastSundayInOctober smalldatetime
declare @Offset smallint
declare @CentralTime smalldatetime
set @YearPart = substring(@DateToConvert,1,4)
--Determine the beginning and ending dates for Daylight Saving Time for the year being converted
--This should work for any date including dates in the past and future that can be stored as a SQLServer SmallDatetime
select @FirstSundayInApril =
case
when datepart(dw,@YearPart + '-04-01') = 1 then @YearPart + '-04-01 02:00:00'
when datepart(dw,@YearPart + '-04-02') = 1 then @YearPart + '-04-02 02:00:00'
when datepart(dw,@YearPart + '-04-03') = 1 then @YearPart + '-04-03 02:00:00'
when datepart(dw,@YearPart + '-04-04') = 1 then @YearPart + '-04-04 02:00:00'
when datepart(dw,@YearPart + '-04-05') = 1 then @YearPart + '-04-05 02:00:00'
when datepart(dw,@YearPart + '-04-06') = 1 then @YearPart + '-04-06 02:00:00'
when datepart(dw,@YearPart + '-04-07') = 1 then @YearPart + '-04-07 02:00:00'
end
select @LastSundayInOctober =
case
when datepart(dw,@YearPart + '-10-31') = 1 then @YearPart + '-10-31 02:00:00'
when datepart(dw,@YearPart + '-10-30') = 1 then @YearPart + '-10-30 02:00:00'
when datepart(dw,@YearPart + '-10-29') = 1 then @YearPart + '-10-29 02:00:00'
when datepart(dw,@YearPart + '-10-28') = 1 then @YearPart + '-10-28 02:00:00'
when datepart(dw,@YearPart + '-10-27') = 1 then @YearPart + '-10-27 02:00:00'
when datepart(dw,@YearPart + '-10-26') = 1 then @YearPart + '-10-26 02:00:00'
when datepart(dw,@YearPart + '-10-25') = 1 then @YearPart + '-10-25 02:00:00'
end
--Calculate the offset for the Central timezone
if @DateToConvert > @FirstSundayInApril and @DateToConvert < @LastSundayInOctober
set @Offset = 5 --Central daylight saving time
else
set @Offset = 6 --Central standard time
--Subtract the offset from the given UTC date
set @CentralTime = dateadd(hh,-@Offset,@DateToConvert)
-- Return the calculated Central Time for the given UTC date
Return (@CentralTime)
end
I hope SOMEONE gets some use out of this thing.
Teague
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply