Convert date in millisecond to date

Question

In my table, I have a column that contains date in millisecond like this:

table a 
      dateinmili
      1440301846096 //first six month date 
      1443589721039 //second six month date

I use that for my Android device and it works fine. When I want to use this time in a PROCEDURE in SQL Server and convert this time to human time (understandable for human) and date I have a problem.

I'm in Iran which uses UTC time in first six Persian date month 4.30 and 3.30 in second six month.

For convert date in PROCEDURE I use this code:

CONVERT(nVARCHAR(10),DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), DATEADD(ss,dateinmili/1000,'1970-01-01')),8) as date 
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), DATEADD(ss,dateinmili/1000,'1970-01-01')) as time

and here is my problem:

When I convert date in second six month and date registered in first six month of year, I get 1 hour difference between real time and converted time. I know that is because

 DATEDIFF(mi, GETUTCDATE(), GETDATE())

method which return different between UTC time and local time when ever its called (in my example return 3:30 not 4:30 ) but I don't know how can I fix that?

I can add column which contain current UTC time but I am looking for another way.

update

I see this question and it's not my problem convert long to date.
My problem is in my country UTC time is not constant in whole year and change between 3.30 and 4.30, for example I have date registered in first six month (Persian six month) like 1440271800000 and convert it now which we are in second six month (Persian six month) and use this code for convert.

declare @unixTS bigint
set @unixTS = 1440271800000

select dateadd(ms, @unixTS%(3600*24*1000), 
    dateadd(day, @unixTS/(3600*24*1000), '1970-01-01 03:30:00.0')
)

I get this

 2015-08-22 23:00:00.000

but it's not right date; the right date is:

2015-08-23 00:00:00.000

because when time registered UTC was 4.30 and not 3.30 but know when I convert it UTC is 3.30.

I wish if there was a method in SQL which return past UTC time different; I mean put a date to that and return that time different between local time and gmt time my problem solved.

I hope you understand my problem.

What do you mean "date in milliseconds"? This is typically a Uni/Linux timestamp, which is the number of ms since a specific time, eg 1/1/1970. You need to know the base time (ie what 0 stands for), to handle this date. If this is indeed a Unix timestamp, you only need to add the number of ms to the base date. What dates do these timestamps correspond to? — Panagiotis Kanavos, Oct 01 '15 at 10:20
PS there's no "human time", different parts of an OS may use different types for the "same" thing. The Unix timestamp is OK for file creation/modification dates but it's completely inappropriate for representing arbitrary dates, much less handle timezones — Panagiotis Kanavos, Oct 01 '15 at 10:23
Possible duplicate of [Convert unix epoch timestamp to TSQL timestamp](http://stackoverflow.com/questions/14507649/convert-unix-epoch-timestamp-to-tsql-timestamp) — Ben, Oct 01 '15 at 11:21

score 0 · Answer 1 · answered Sep 01 '16 at 18:56

In the US we have Daylight Savings Time in the summer, in most areas that means that we are also not fixed offset from UTC. Older versions of MS Dynamics CRM used to save everything in UTC, so when we wanted to export data in local time, we had a similar exercise. I created a set of SQL functions that would take the standard GMT offset and the datetime to I wanted to convert and figure out whether to apply the standard or DST offset and return the local datetime. If your offset follows a set of rules, then you can modify this:

    CREATE function [dbo].[DC_GMTtoLocal]
    (@OrigGMT datetime, 
@StandardOffset int)
RETURNS datetime
AS
BEGIN
DECLARE @RevDate datetime

set @RevDate = CASE dbo.DC_DaylightSavingTime_IsInEffect(@OrigGMT)
    WHEN 1 THEN DATEADD(hour, - @StandardOffset + 1, @OrigGMT) --  in DST
    ELSE DATEADD(hour, - @StandardOffset, @OrigGMT) -- Not In DST
    END

return @RevDate 
END


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[DC_DaylightSavingTime_IsInEffect]
(@DtTime datetime)
RETURNS tinyint
AS
BEGIN
DECLARE @DLSStart datetime
, @DLSEnd datetime
, @DLSActive tinyint

SET @DLSActive = 0
If DATEADD(YEAR,3,GETDATE()) > @DtTime 
BEGIN
    SET @DLSStart =(SELECT dbo.DC_GetDaylightSavingsTimeStart(CONVERT(varchar,DATEPART(YEAR,@DtTime))))
    SET @DLSEnd =(SELECT dbo.DC_GetDaylightSavingsTimeEnd(CONVERT(varchar,DATEPART(YEAR,@DtTime))))


    IF @DtTime BETWEEN @DLSStart AND @DLSEnd 
    BEGIN
        SET @DLSActive = 1 
    END
        --SET @DLSActive = 0
END

RETURN @DLSActive 
END

GO

CREATE function [dbo].[DC_GetDaylightSavingsTimeStart]
(@Year varchar(4))
RETURNS smalldatetime
as
--Start date: We evaluate the day of the week corresponding to the first day of the month and find the second Sunday of March using a Case statement 
begin
declare @DTSStartWeek smalldatetime, @DTSEndWeek smalldatetime
set @DTSStartWeek = '03/01/' + convert(varchar,@Year)
return case datepart(dw,@DTSStartWeek)
when 1 then 
dateadd(hour,170,@DTSStartWeek)
when 2 then
dateadd(hour,314,@DTSStartWeek)
when 3 then 
dateadd(hour,290,@DTSStartWeek)
when 4 then 
dateadd(hour,266,@DTSStartWeek)
when 5 then 
dateadd(hour,242,@DTSStartWeek)
when 6 then 
dateadd(hour,218,@DTSStartWeek)
when 7 then
dateadd(hour,194,@DTSStartWeek)
end
end


GO

CREATE function [dbo].[DC_GetDaylightSavingsTimeEnd]
(@Year varchar(4))
RETURNS smalldatetime
as
-- End date: We evaluate the day of the week corresponding to the first day of the month and find the first Sunday of March using a Case statement 
begin
declare @DTSEndWeek smalldatetime
set @DTSEndWeek = '11/01/' + convert(varchar,@Year)
return case datepart(dw,dateadd(week,1,@DTSEndWeek))
when 1 then
dateadd(hour,2,@DTSEndWeek)
when 2 then
dateadd(hour,146,@DTSEndWeek)
when 3 then
dateadd(hour,122,@DTSEndWeek)
when 4 then
dateadd(hour,98,@DTSEndWeek)
when 5 then 
dateadd(hour,74,@DTSEndWeek)
when 6 then 
dateadd(hour,50,@DTSEndWeek)
when 7 then 
dateadd(hour,26,@DTSEndWeek)
end
end


GO

Convert date in millisecond to date

1 Answers1