Convert Utc Milliseconds to Datetime in SQL Server

Convert UTC Milliseconds to DATETIME in SQL server

DECLARE @UTC BIGINT
SET @UTC = 1348203320997 

SELECT DATEADD(MILLISECOND, @UTC % 1000, DATEADD(SECOND, @UTC / 1000, '19700101'))

Converting integer milliseconds value to date time format in sql server

Try this:

TSQL:

SELECT DATEADD(SECOND,1465815600,'1970-1-1')

MYSQL:

SELECT DATE_ADD('1970-1-1',INTERVAL 1465815600 SECOND)

Why SECOND? because given 1465815600000 is greater than the max integer value and that would cause the arithmetic overflow exception, so in order to prevent it, we divide it by 1000 and use SECOND instead of MILISECOND

MSSQL - Convert milliseconds since 1970 to datetime2

Using the formula from @Mikeal Eriksson's answer here.

I would convert the float to a bigint and then create the datetime:

select 
  DATEADD(MILLISECOND, 
          cast(dateModified as bigint) % 1000, 
          DATEADD(SECOND, cast(dateModified as bigint) / 1000, '19700101'))
from sometable

See SQL Fiddle with Demo

Convert string to datetime in SQL, when Z and T literals are involved

DATETIME and SMALLDATE are legacy types(as in replaced-15-years-ago, don't-use legacy) that have a lot of quirks and limited precision. For example, datetime is only accurate to 0, 3 or 7 milliseconds. The value you tried to parse can't be converted to a datetime without losing precision.

The docs warn strongly against using this type, with a big pink warning at the top of the DATETIME page:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

In this case you need the datetime2 or datetimeoffset types introduced in 2005. Both types allow specifying a precision.

To preserve the timezone offset, use datetimeoffset.

select CONVERT(datetimeoffset, '2021-12-15T14:18:22.6496978Z', 127)
----
2021-12-15 14:18:22.6496978 +00:00

To remove the offset, use datetime2. The result will have no assumed offset so you should take care to always treat it as UTC:

select CONVERT(datetimeoffset, '2021-12-15T14:18:22.6496978Z', 127)
----
2021-12-15 14:18:22.6496978

In both cases you can specify the desired precision. For example, datetime2(0) will truncate fractional seconds:

select CONVERT(datetime2(0), '2021-12-15T14:18:22.6496978Z', 127)
---
2021-12-15 14:18:23

MSSQL bigint Unix Timestamp to Datetime with milliseconds

I think that you are dealing with nanosecond precision. What you can get in native sql is 100ns precision.

declare @ts as bigint = 1462924862735870900

select dateadd(NANOSECOND, @ts % 1000000000, dateadd(SECOND, @ts / 1000000000, cast('1970-01-01' as datetime2(7))))

The outcome is 2016-05-11 00:01:02.7358709

---

Related Topics