Converting integer milliseconds value to date time format in sql server

Question

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?

for which database? for mysql it's as trivial as `from_unixtime($ts/1000)` — Marc B, Jun 22 '16 at 20:28
Look [here](http://stackoverflow.com/questions/12524537/convert-utc-milliseconds-to-datetime-in-sql-server) — BJones, Jun 22 '16 at 20:32
If any of these suggestions helped, upvote / select an answer to support your peepz — Kellen Stuart, Jun 22 '16 at 23:26

score 3 · Accepted Answer · answered Jun 22 '16 at 20:34

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

Converting integer milliseconds value to date time format in sql server

1 Answers1