troubleshooting Question

Sql query time format modification

Avatar of Sean
SeanFlag for United States of America asked on
DatabasesSQL
11 Comments1 Solution542 ViewsLast Modified:
Hi,
I need to modify this query's out put with regards to the " time spent" filed.
Here is what I have:

SELECT sinfo.WORKSTATIONNAME "Workstation",wo.WORKORDERID "Request ID",scd.NAME "Sub Category",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00')  "Completed Time",
cast((((wo.timespentonreq)/1000)/3600) as varchar(20)) +':'+cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20))+':'+ cast((((wo.timespentonreq)%1000)%60) as varchar(20)) "Time Spent",
wof.UDF_CHAR3 "Location"
FROM WorkOrder_Threaded wot
INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID
LEFT JOIN SystemInfo sinfo ON wo.WORKSTATIONID=sinfo.WORKSTATIONID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID
WHERE  (((DATEADD(s,wo.CREATEDTIME/1000,'01-01-1970 05:30')> convert(varchar,'2009-09-27 12:00',21) AND ((wo.CREATEDTIME != 0)
AND (wo.CREATEDTIME IS NOT NULL))) AND (DATEADD(s,wo.CREATEDTIME/1000,'01-01-1970 05:30') <= convert(varchar,'2009-09-28 11:59',21)
AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))
AND (cd.CATEGORYNAME = N'Cajeros Only' COLLATE SQL_Latin1_General_CP437_CS_AS)) AND wot.THD_WOID=wot.WORKORDERID

Which produces these results the attached  .csv  file.
How do I get it so that the "Time Spent " filed is in this format?

2:54:00
4:20:00
3:19:00

Thank you.
test.csv
ASKER CERTIFIED SOLUTION
leakim971
Multitechnician
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros