question

jaymz69 avatar image
jaymz69 asked

Calculate Start and End Time that are Text

The data I get is in text from the DB (74125 for 7:41:25) I need to take the start and end times and calculate the hh:mm:ss what is the best approach in SQL Server 2008 R2. Start End ElapsedTime 70500 71002 00:05:02 Thanks ****UPDATE**** I just noticed the time is not string but it is being stored as int.
timecalculations
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
In this example I show the intermediate steps to show, but you don't need to include these... declare @YourTable table (StartTimeText varchar(10), EndTimeText varchar(10)) insert into @YourTable select '70500', '71002' select *, cast( substring(right('000000'+StartTimeText,6),1,2) + ':' + substring(right('000000'+StartTimeText,6),3,2) + ':' + substring(right('000000'+StartTimeText,6),5,2) as datetime), cast( substring(right('000000'+EndTimeText,6),1,2) + ':' + substring(right('000000'+EndTimeText,6),3,2) + ':' + substring(right('000000'+EndTimeText,6),5,2) as datetime), cast( cast( substring(right('000000'+EndTimeText,6),1,2) + ':' + substring(right('000000'+EndTimeText,6),3,2) + ':' + substring(right('000000'+EndTimeText,6),5,2) as datetime) - cast( substring(right('000000'+StartTimeText,6),1,2) + ':' + substring(right('000000'+StartTimeText,6),3,2) + ':' + substring(right('000000'+StartTimeText,6),5,2) as datetime) as time) as ElapsedTime from @YourTable gives StartTimeText EndTimeText ElapsedTime ------------- ----------- ----------------------- ----------------------- ---------------- 70500 71002 1900-01-01 07:05:00.000 1900-01-01 07:10:02.000 00:05:02.0000000
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
This should run on any version. --First the integer version DECLARE @YourTable TABLE (StartTime int, EndTime int) INSERT INTO @YourTable SELECT 70500, 71002 INSERT INTO @YourTable SELECT 120501, 121845 SELECT StartTime, EndTime, CONVERT(CHAR(8), (DATEADD(SECOND, DATEDIFF(SECOND, CONVERT(DATETIME,CONVERT(VarCHAR(02),Starttime/10000)+':' +CONVERT(VARCHAR(02),(StartTiME/100)%100)+':' +CONVERT(VARCHAR(02),StartTime%100),8), CONVERT(DATETIME,CONVERT(VarCHAR(02),Endtime/10000)+':' +CONVERT(VARCHAR(02),(EndTime/100)%100)+':' +CONVERT(VARCHAR(02),EndTime%100),8)), CONVERT(DATETIME, '00:00:00', 113))),8) AS ElapsedTime FROM @YourTable --And now the string version DECLARE @YourTable TABLE (StartTimeText VARCHAR(10), EndTimeText VARCHAR(10)) INSERT INTO @YourTable SELECT '70500', '71002' INSERT INTO @YourTable SELECT '120501', '121845' SELECT StartTimeText, EndTimeText, CONVERT(CHAR(8), (DATEADD(SECOND, DATEDIFF(SECOND, CONVERT(DATETIME, STUFF(STUFF(LEFT('00', 6 - LEN(StartTimeText)) + StartTimeText, 5, 0, ':'), 3, 0, ':'), 8), CONVERT(DATETIME, STUFF(STUFF(LEFT('00', 6 - LEN(EndTimeText)) + EndTimeText, 5, 0, ':'), 3, 0, ':'), 8)), CONVERT(DATETIME, '00:00:00', 113))), 8) AS ElapsedTime FROM @YourTable
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Phil factor Nice.. Liked the STUFFs stuff :) But I guess the code could get shorten quite a bit....? SELECT StartTimeText, EndTimeText, CONVERT(CHAR(8), CONVERT(DATETIME, STUFF(STUFF(LEFT('00',6 - LEN(EndTimeText)) + EndTimeText, 5, 0, ':') , 3, 0, ':'))- CONVERT(DATETIME, STUFF(STUFF(LEFT('00',6 - LEN(StartTimeText)) + StartTimeText, 5, 0, ':') , 3, 0,':')) , 108) AS ElapsedTime FROM @YourTable Also, two very good answers but just wandering what about handling more than 24 hours time span? :) NULL values handling could always be user's discrestion.
0 Likes 0 ·
@robbin. Yes, I'd forgotten about the Date and Time Style no. 8 I felt so ashamed, I went and altered the code. Yours works fine, but was originally worried I might be taken to task for doing a direct subtraction of one datetime from another!
0 Likes 0 ·
I've added in the integer version
0 Likes 0 ·
jaymz69 avatar image
jaymz69 answered
The measurement is a daily query. The activity is measured with the getdate() of that dady only so it all will be under 24 hours. The users are pulling orders within a time period. if anything could check if > 1800 since they would never go past that time daily.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I just noticed the time is not string but it is being stored as int.
0 Likes 0 ·
jaymz69 avatar image
jaymz69 answered
I just noticed the time is not string but it is being stored as int.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've added an integer solution
0 Likes 0 ·
jaymz69 avatar image
jaymz69 answered
So now I get this error when I change the variables to int Msg 242, Level 16, State 3, Line 10 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. That error matches the query I am using for the real data too
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Even though you are storing as an int, they are not really integers, as you can't have a value of 76666, for example. They are a string of numbers that represent a time. So going back to my example, simply convert the ints to varchars, and carry on... declare @YourTable table (StartTimeText int, EndTimeText int) insert into @YourTable select '70500', '71002' select *, cast( substring(right('000000'+cast(StartTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),5,2) as datetime), cast( substring(right('000000'+cast(EndTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),5,2) as datetime), cast( cast( substring(right('000000'+cast(EndTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),5,2) as datetime) - cast( substring(right('000000'+cast(StartTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),5,2) as datetime) as time) as ElapsedTime from @YourTable
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I added the CONVERT(VARCHAR(8) your code...) to get ride of the milliseconds and it is perfect! Thanks
0 Likes 0 ·
jaymz69 avatar image
jaymz69 answered
How can I get the AVERAGE now? I tried to wrap it all with AVG() but of course errored out: SELECT AVG(CONVERT(varchar(8),cast( cast( substring(right('000000'+cast(rptime as varchar),6),1,2) + ':' + substring(right('000000'+cast(rptime as varchar),6),3,2) + ':' + substring(right('000000'+cast(rptime as varchar),6),5,2) as datetime) - --Do the math on the rptime(End) and rfotime(start) time to get the elapsed time cast( substring(right('000000'+cast(rfotime as varchar),6),1,2) + ':' + substring(right('000000'+cast(rfotime as varchar),6),3,2) + ':' + substring(right('000000'+cast(rfotime as varchar),6),5,2) as datetime) as time))) AS AvgTimePick
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DECLARE @YourTable TABLE (StartTime int, EndTime int) INSERT INTO @YourTable SELECT 70500, 71002 INSERT INTO @YourTable SELECT 120501, 121845 Select CONVERT(CHAR(8), DATEADD(SECOND, AVG(DATEDIFF(SECOND, CONVERT(DATETIME,CONVERT(VarCHAR(02),Starttime/10000)+':' +CONVERT(VARCHAR(02),(StartTiME/100)%100)+':' +CONVERT(VARCHAR(02),StartTime%100),8), CONVERT(DATETIME,CONVERT(VarCHAR(02),Endtime/10000)+':' +CONVERT(VARCHAR(02),(EndTime/100)%100)+':' +CONVERT(VARCHAR(02),EndTime%100),8))), CONVERT(DATETIME, '00:00:00', 113)),8) AS ElapsedTime FROM @YourTable
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.