Menu
I want to convert UTC milliseconds to DateTime in SQL server.
This can easily be done in C# by following code:
DateTime startDate = new DateTime(1970, 1, 1).AddMilliseconds(1348203320000); I need to do this in SQL server. I found some script here, but this was taking initial ticks from 1900-01-01.
I have used the DATEADD function as below, but this was giving an arithmetic overflow exception by supping milliseconds as difference:
SELECT DATEADD(MILLISECOND,1348203320000,'1970-1-1') How can I do the conversion properly?
943
SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETUTCDATE()), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS LOCAL_IST; Here, the GETUTCDATE() function can be used to get the current date and time UTC. Using this query the UTC gets converted to local IST.
SQL Server GETUTCDATE() Function The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss.
To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.
DECLARE @UTC BIGINT SET @UTC = 1348203320997 SELECT DATEADD(MILLISECOND, @UTC % 1000, DATEADD(SECOND, @UTC / 1000, '19700101')) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
