Solved

Convert Time stored in milliseconds integer to hh:mm datetime SQL Server

Posted on 2010-09-17
10
2,879 Views
Last Modified: 2012-05-10
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.

Thanks!
0
Comment
Question by:BrotherForbes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 21

Expert Comment

by:mastoo
ID: 33702377
Hours and minutes?

Select Left( Convert(varchar(10), DateAdd(ms, your number here, Convert(DateTime, '1/1/2000')), 8), 5)
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33702392
Try this:  
-- add the milliseconds to an arbitrary date
-- convert this format to HH:MM:SS


SELECT JOB_NAME, MAIL_SERVER, TASK_SERVER, convert(char(8), dateadd(ms, JOB_START_TIME, '2001-01-01'), 8) AS [START_TIME]
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33702669
Or this:

SELECT RIGHT('0' + CAST(JOB_START_TIME / 1000 / 60 / 60 AS varchar(2)), 2) + ':' + RIGHT('0' + CAST(JOB_START_TIME / 1000 / 60 % 60 AS varchar(2)), 2)
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:BrotherForbes
ID: 33702900
Mastoo and ScottPletcher, both your methods are working perfectly in SQL and Excel!  Only one thing I forgot to mention, it is putting the time in 24hr format.  Can we get it in 12 hour format with AM and PM?  OR is that a separate question?
0
 

Author Comment

by:BrotherForbes
ID: 33702932
tlovie: your method is working also, just don't need seconds and need to get it in 12 hour time format.
0
 
LVL 7

Accepted Solution

by:
tlovie earned 500 total points
ID: 33703089
Try this:

SELECT JOB_NAME, MAIL_SERVER, TASK_SERVER, substring(convert(varchar(20), dateadd(ms, JOB_START_TIME, '2001-01-01'), 100), 12, 8) AS [START_TIME]
0
 

Author Comment

by:BrotherForbes
ID: 33703169
Thanks tlovie...that is just about perfect... Is it possible to add a space between the mm and the AM/PM indicator?
0
 

Author Closing Comment

by:BrotherForbes
ID: 33703187
This resolves my question
0
 

Author Comment

by:BrotherForbes
ID: 33703219
tlovie, if you want to comment on the question about spacing please do, but I've already given you credit for the answer since it solves my original issue completely, cosmetics aside.  Thanks to all Experts!!
0
 

Author Comment

by:BrotherForbes
ID: 33703322
For future users who might have a similar question here is an example of the data in the table



experts-26481020.xls
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question