Convert Time stored in milliseconds integer to hh:mm datetime SQL Server
Posted on 2010-09-17
In a table of scheduled jobs, the JOB_START_TIME is mysteriously stored in milliseconds counting from midnight as data type integer.
I need help to convert these values to hh:mm datetime format so I can make a user-friendly excel report of all the scheduled jobs.
If I don't convert JOB_START_TIME to NUMERIC or DECIMAL, it truncates the time so it is only accurate up to the hour:
SELECT JOB_NAME, MAIL_SERVER, TASK_SERVER, ((((JOB_START_TIME/1000))/60)/60) AS [START_TIME]
So I converted it and what I got was the hour plus a decimal portion representing the fraction of the hour but I don't know how to render that all accurately in hh:mm datetime format:
SELECT JOB_NAME, MAIL_SERVER, TASK_SERVER, ((((CONVERT(NUMERIC,JOB_START_TIME)/1000))/60)/60) AS [START_TIME]
Experts, can you help me get this into a single SQL statement without temp tables, functions, or variables? I've had difficulty running T-SQL containing the aforementioned from Excel 2003. Other reporting systems are also not an option.