x

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.
more ▼

asked Apr 26 '12 at 10:07 PM in Default

jaymz69 gravatar image

jaymz69
172 8 10 13

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

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
more ▼

answered Apr 27 '12 at 08:14 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Apr 27 '12 at 12:50 PM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

@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.
Apr 27 '12 at 01:37 PM robbin
@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!
Apr 27 '12 at 02:23 PM Phil Factor
I've added in the integer version
Apr 27 '12 at 05:15 PM Phil Factor
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Apr 27 '12 at 02:32 PM

jaymz69 gravatar image

jaymz69
172 8 10 13

I just noticed the time is not string but it is being stored as int.
Apr 27 '12 at 03:42 PM jaymz69
(comments are locked)
10|1200 characters needed characters left
I just noticed the time is not string but it is being stored as int.
more ▼

answered Apr 27 '12 at 02:48 PM

jaymz69 gravatar image

jaymz69
172 8 10 13

I've added an integer solution
Apr 27 '12 at 05:16 PM Phil Factor
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 27 '12 at 03:45 PM

jaymz69 gravatar image

jaymz69
172 8 10 13

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28
x24

asked: Apr 26 '12 at 10:07 PM

Seen: 1307 times

Last Updated: Apr 30 '12 at 02:21 PM