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, 2012 at 10:07 PM in Default

avatar image

jaymz69
172 11 13 17

(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, 2012 at 08:14 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(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, 2012 at 12:50 PM

avatar image

Phil Factor
4.2k 8 23 20

@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, 2012 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, 2012 at 02:23 PM Phil Factor

I've added in the integer version

Apr 27, 2012 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, 2012 at 02:32 PM

avatar image

jaymz69
172 11 13 17

I just noticed the time is not string but it is being stored as int.

Apr 27, 2012 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, 2012 at 02:48 PM

avatar image

jaymz69
172 11 13 17

I've added an integer solution

Apr 27, 2012 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, 2012 at 03:45 PM

avatar image

jaymz69
172 11 13 17

(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.

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:

x36
x11

asked: Apr 26, 2012 at 10:07 PM

Seen: 1842 times

Last Updated: Apr 30, 2012 at 02:21 PM

Copyright 2016 Redgate Software. Privacy Policy