CODEWITHSUNDEEP
sqlsql-serverdatabasedatetimetimestamp
williamfaith
williamfaith

Reputation: 277

Convert timestamp into datetime

I'm using a SQL Server database and I have a datetime column.

Now I want to convert the timestamp into regular datetime format.

SELECT
    [datetime]
FROM [database].[dbo].[data]

datetime
1584538200000
1584538260000
.............
1584538620000

Can anyone help?

Upvotes: 0

Views: 280

Answers (2)

Wilhelm
Wilhelm

Reputation: 306

Gordon is in general right but I think it is seconds since cdate (1970-01-01) and not milliseconds, so try

SELECT DATEADD(second, [datetime], '1970-01-01')
FROM [database].[dbo].[data]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

These look like Unix timestamps. You can convert by adding seconds since 1970-01-01:

SELECT DATEADD(second, [datetime] / 1000, '1970-01-01')
FROM [database].[dbo].[data]

Upvotes: 2

Related Questions