Start Free TrialLog in
Avatar of Sean
SeanFlag for United States of America

asked on 

Sql query time format modification

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
DatabasesSQL
Avatar of undefined
Last Comment
Sean
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Hello slimlcd101,

Try this :


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)/3600) as varchar(20)) +':'+cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20))+':'+ REPLACE(REPLACE(LEN(cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'1','0'+cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'2',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

Open in new window

Avatar of Sean
Sean
Flag of United States of America image

ASKER

leakim971,
Please see the results in test2.csv.
thank you for your help
S
test2.csv
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Seems I copy/cut too much...

Corrected :


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))+':'+ REPLACE(REPLACE(LEN(cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'1','0'+cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'2',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

Open in new window

Avatar of Sean
Sean
Flag of United States of America image

ASKER

Leakim971,
this might be a better looking output.
2009-09-27 03:02:15.000      2009-09-27 12:05:59.000      4:5:4:5:00      South California
2009-09-27 10:06:17.000      2009-09-27 10:19:31.000      0:13:0:13:00      South California
2009-09-27 11:06:37.000      2009-09-27 13:03:45.000      1:57:1:57:00      North California
2009-09-27 11:07:38.000      2009-09-27 12:54:40.000      1:47:1:47:00      South California
2009-09-27 13:56:13.000      2009-09-27 15:22:31.000      1:26:1:26:00      South California
2009-09-27 15:02:49.000      2009-09-27 15:37:37.000      0:34:0:34:00      South California
2009-09-27 02:58:12.000      2009-09-27 12:05:27.000      4:5:4:5:00      South California
2009-09-27 11:08:38.000      2009-09-27 13:02:10.000      1:53:1:53:00      South California
2009-09-27 17:12:37.000      2009-09-27 18:55:29.000      1:42:1:42:00      South California
2009-09-27 21:23:54.000      2009-09-28 10:55:58.000      2:55:2:55:00      South California
2009-09-27 00:20:15.000      2009-09-27 11:56:03.000      3:56:3:56:00      South California
2009-09-27 04:49:35.000      2009-09-27 13:03:19.000      5:3:5:3:00      South California
2009-09-27 07:42:59.000      2009-09-27 14:06:25.000      6:6:6:6:00      Chicago, IL
2009-09-27 14:33:26.000      2009-09-27 14:55:04.000      0:21:0:21:00      South California
2009-09-27 15:21:03.000      2009-09-27 15:48:58.000      0:27:0:27:00      South California
2009-09-27 19:33:04.000      2009-09-28 10:41:49.000      3:8:3:8:00      North California

thanks
S
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Leakin971,
This is from your second post, not quit there,  
2009-09-27 03:02:15.000      2009-09-27 12:05:59.000      4:5:00      South California
2009-09-27 13:56:13.000      2009-09-27 15:22:31.000      1:26:00      South California
2009-09-27 15:02:49.000      2009-09-27 15:37:37.000      0:34:00      South California
2009-09-27 02:58:12.000      2009-09-27 12:05:27.000      4:5:00      South California
2009-09-27 21:23:54.000      2009-09-28 10:55:58.000      2:55:00      South California
2009-09-27 00:20:15.000      2009-09-27 11:56:03.000      3:56:00      South California
2009-09-27 04:49:35.000      2009-09-27 13:03:19.000      5:3:00      South California
2009-09-27 07:42:59.000      2009-09-27 14:06:25.000      6:6:00      Chicago, IL
2009-09-27 19:33:04.000      2009-09-28 10:41:49.000      3:8:00      North California

Thanks
S
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Don't think you want to have two digits for hour so :


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)) +':'+REPLACE(REPLACE(LEN(cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20))),'1','0'+cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20))),'2',cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20)))+':'+ REPLACE(REPLACE(LEN(cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'1','0'+cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'2',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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Leakim971,
 you got it. Thank you. Here is the output.
2009-09-27 03:02:15.000      2009-09-27 12:05:59.000      4:05:00      South California
2009-09-27 13:56:13.000      2009-09-27 15:22:31.000      1:26:00      South California
2009-09-27 15:02:49.000      2009-09-27 15:37:37.000      0:34:00      South California
2009-09-27 02:58:12.000      2009-09-27 12:05:27.000      4:05:00      South California
2009-09-27 11:08:38.000      2009-09-27 13:02:10.000      1:53:00      South California
2009-09-27 17:12:37.000      2009-09-27 18:55:29.000      1:42:00      South California
2009-09-27 21:23:54.000      2009-09-28 10:55:58.000      2:55:00      South California
2009-09-27 00:20:15.000      2009-09-27 11:56:03.000      3:56:00      South California
2009-09-27 04:49:35.000      2009-09-27 13:03:19.000      5:03:00      South California
2009-09-27 07:42:59.000      2009-09-27 14:06:25.000      6:06:00      Chicago, IL
2009-09-27 14:33:26.000      2009-09-27 14:55:04.000      0:21:00      South California
2009-09-27 15:21:03.000      2009-09-27 15:48:58.000      0:27:00      South California
2009-09-27 19:33:04.000      2009-09-28 10:41:49.000      3:08:00      North California


Much appreciated.
Sean
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Leakim971,

The sql query runs just fine,thank you.
when I import the data to an Excel sheel the format is as you see below, it looks fine but when I try to find the total "time spent" my formula only works if there is not 0 next to the "hour" value, ie..2:54:00
,3:19:00,3:06:00. is there anything that can be done on sql query side or Excel side?
Thanks again.


10/9/2009 23:05      10/10/2009 11:37      03:37:00      North California
10/9/2009 0:32      10/9/2009 12:20      04:20:00      North California
10/9/2009 1:23      10/9/2009 11:15      03:15:00      South California
10/9/2009 7:32      10/9/2009 14:13      06:13:00      North California
10/9/2009 3:02      10/9/2009 11:09      03:09:00      South California
10/9/2009 7:38      12/31/1969 17:00      00:00:00      Chicago, IL
10/9/2009 10:46      10/9/2009 11:01      00:14:00      South California
10/9/2009 14:57      10/9/2009 18:11      03:13:00      South California
10/9/2009 18:00      10/10/2009 12:14      06:14:00      North California
10/9/2009 21:20      10/10/2009 10:12      02:12:00      South California
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Post ID 25544732 I don't see any zero.
Assuming Excel is formatting the hour when you past results, you need to work on it :
Right click Time Spent column, right click cell format, custom and set h:mm:ss


Avatar of Sean
Sean
Flag of United States of America image

ASKER

I see. Very well thank you for your help . will be posting another question shortly with regards to the same query.
Databases
Databases

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent