CODEWITHSUNDEEP
sql-serverdatetime
user1960217
user1960217

Reputation: 131

Converting integer milliseconds value to date time format in sql server

I have a column called start time which is populated with a number like "1465815600000" and increasing. This column is the number of milliseconds from the year 1970 jan 1st 12.00:00:000 AM to a certain date in june 2016. This is in integer, which I need to convert it to date time format.

EX: 1465815600000 => 2016-06-12-12:00:00 (something like this)

Can somebody help me with to write this function?

Upvotes: 0

Views: 9241

Answers (1)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

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

Upvotes: 4

Related Questions