CODEWITHSUNDEEP
sql-server
Paul
Paul

Reputation: 421

mssql - CONVERT INT value to datetime

At this moment I'm getting records by modify datetime via: 

WHERE modify_date >= ( SELECT DATEDIFF( s, CONVERT (datetime, ''1990-01-01'', 120), CONVERT (datetime, '+@theDate+', 120) ) )

where @theDate is for e.g.:

SET @theDate = '''2018-06-04 00:00:01'''

In column I have BIGINT value, e.g. 897004800 - how can I convert this value to clear datetime?

Upvotes: 0

Views: 87

Answers (2)

Alexey Sumin
Alexey Sumin

Reputation: 62

If your bigint number is UNIX timestamp, yo should start from '1970-01-01':

DECLARE @unix_timestamp BIGINT = 883612800
, @start_from DATETIME = '1970-01-01'
SELECT DATEADD(SECOND, @unix_timestamp, @start_from)

Upvotes: 1

EzLo
EzLo

Reputation: 14189

Just go the other way arround.

DECLARE @value BIGINT = 883612800

SELECT DATEADD(SECOND, @value, '1990-01-01')

Your modify_date is the amount of seconds between your hard-coded date of 1990 and the actual modified date. If you want to display the actual modified date, just add that amount of seconds to 1990.

Upvotes: 3

Related Questions