Convert time in milliseconds to datetime format?

Question

I have a column in MSSQL database that stores time in milliseconds (based on 01 jan 1970).

I want to convert the time from milliseconds to Date Time Format (dd/mm/yy) .

Are there any good ways to convert within MSSQL?

Answer 1

Check out the to_date command, assuming MSSQL allows it. You should be able to do something like:



select to_date('19700101','YYYYMMDD') + (column_name/(1000*60*60*24)) from table_name;



It helps to know that when adding numbers to dates, the number is treated as a number of days.

Answer 2

Hi

one year has

32421600 seconds

so you just minus this number from the total Number, you get the year then to get months + days + hours, minutes and seconds in the remaining no the divide it on 60 you will get hours divide by 24 you get days ( total days) remaining divide it on 60 you will get minutes the rest will be seconds ( remember you are using milliseconds so first make it second ( take the last 2 digest out)

good luck

Answer 3

Dear Warthog,



I would suggest using a php script to run the date() command.