Perform math on string

  • My developer is storing the time as a 6 char string, e.g. 133737 for 1:37:37 PM. I'm trying to subtract fields to determine number of hours minutes seconds between events for elapsed time, but I can't seem to get CAST or CONVERT to give me the required format.

    Row 1 154040

    Row 2 133737

    So, (Row 1 - Row 2) = 2h3m3s or 2.05h

    The sun is bright today, but I'm not.

  • First, fire your developer and store the time in a datetime field.

    In the meantime, use this code:

    declare @Start char(6), @End char(6)

    set @Start = '133737'

    set @End = '154040'

    declare @StartTime datetime, @EndTime datetime

    set @StartTime = stuff(stuff(@Start,3,0,':'),6,0,':')

    set @EndTime = stuff(stuff(@End,3,0,':'),6,0,':')

    select @StartTime, @EndTime, convert(char(8), @EndTime - @StartTime, 108)

    After you've changed the fields to datetime, all you would have to do is:

    select convert(char(8), EndDate - StartDate, 108)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Before you start stuffing the string, make sure that times before 10 AM are stored with leading zeroes. If not, you might have a problem when 9:00 AM tries to turn into the hour 90.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • By converting the time strings to datetime values, you can do the following.

    /* I'm assuming here that @TimeString1 always represents the earlier time and @TimeString2 the later time */

    DECLARE @TimeString1 char(6)

    DECLARE @TimeString2 char(6)

    SELECT @TimeString1 = '133737', @TimeString2 = '154040'

    /* Insert ':' characters to format as 'HH:MM:SS' and implicitly convert strings to datetime values */

    DECLARE @Time1 datetime

    DECLARE @Time2 datetime

    SELECT @Time1 = STUFF(STUFF(@TimeString1, 5, 0, ':'), 3, 0, ':'),

    @Time2 = STUFF(STUFF(@TimeString2, 5, 0, ':'), 3, 0, ':')

    /* Determine time difference as a datetime value relative to value 0 (1900-01-01 00:00:00.000). Note that if @Time1 > @Time2 then the result of the DATEDIFF function call is negative. In this case, it is assumed that the two times span a day boundary so 24 * 60 * 60 = 86400 seconds need to be added to get the true elapsed time. */

    DECLARE @ElapsedTime datetime

    SELECT @ElapsedTime = DATEADD(second, DATEDIFF(second, @Time1, @Time2) + CASE WHEN (@Time1 > @Time2) THEN 86400 ELSE 0 END, 0)

    /* Finally display elapsed time using CONVERT format 108 */

    SELECT CONVERT(char(8), @ElapsedTime, 108)

    If elapsed times equal or exceed 24 hours then the above method won't work and you will need to consider dates as well.

    It would have been simpler and more efficient to store the times as datetime values in the table, and if you are storing the date in a separate column, then it would probably be better to store the date and time combined in a single datetime column as well.

  • Here's a one-command approach for the first one I did. It's uglier, but you don't have to call any procedures / functions.

    select convert(char(8),

    convert(datetime,

    convert(datetime, stuff(stuff('154040',3,0,':'),6,0,':')) -

    convert(datetime, stuff(stuff('133737',3,0,':'),6,0,':'))),

    108)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thanks guys. I've been away from SQL for a while. This really helped.

  • WayneS (4/24/2009)


    First, fire your developer and store the time in a datetime field.

    BWAA-HAA-HAA!!!! Dammit! Ya beat me to it! 😛 Wonder if the developer likes point blank pork chops? :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • GSquared (4/24/2009)


    Before you start stuffing the string, make sure that times before 10 AM are stored with leading zeroes. If not, you might have a problem when 9:00 AM tries to turn into the hour 90.

    Hmm, good point.

    How about this?

    select convert(char(8),

    convert(datetime,

    convert(datetime, stuff(stuff(right('0' + '154040',6),3,0,':'),6,0,':')) -

    convert(datetime, stuff(stuff(right('0' + '133737',6),3,0,':'),6,0,':'))),

    108)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (4/24/2009)


    GSquared (4/24/2009)


    Before you start stuffing the string, make sure that times before 10 AM are stored with leading zeroes. If not, you might have a problem when 9:00 AM tries to turn into the hour 90.

    Hmm, good point.

    How about this?

    select convert(char(8),

    convert(datetime,

    convert(datetime, stuff(stuff(right('0' + '154040',6),3,0,':'),6,0,':')) -

    convert(datetime, stuff(stuff(right('0' + '133737',6),3,0,':'),6,0,':'))),

    108)

    Good idea... but what about times before 1 AM? For example, 000001 should be interpreted as 00:00:01 and it probably won't because it seems that all the leading zeros are dropped... probably because the original time was stored as an INT or something odd. I say "odd", but even Microsoft made the same terrible mistake in the MSDB.dbo.SysJobsHistory.

    With that in mind, you might want to change the code to look like this...

    select convert(char(8),

    convert(datetime,

    convert(datetime, stuff(stuff(right('000000' + '154040',6),3,0,':'),6,0,':')) -

    convert(datetime, stuff(stuff(right('000000' + '133737',6),3,0,':'),6,0,':'))),

    108)

    Still, that doesn't solve the real problem... the real problem is that the times aren't stored as a DATETIME datatype which would greatly simplify such mathematics.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (4/27/2009)


    To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.

    As is usually the case, absolutely correct. But, reversing it is relatively expensive.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/27/2009)


    GSquared (4/27/2009)


    To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.

    As is usually the case, absolutely correct. But, reversing it is relatively expensive.

    Both of the methods suck.

    🙂

    _____________
    Code for TallyGenerator

  • Sergiy (4/27/2009)


    Jeff Moden (4/27/2009)


    GSquared (4/27/2009)


    To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.

    As is usually the case, absolutely correct. But, reversing it is relatively expensive.

    Both of the methods suck.

    🙂

    Heh... Long time no see. Speaking of "see"... got a method that doesn't suck so much? I've forgotten the pure math solution because I don't allow such garbage into my db's to begin with. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Off the bat- First post 😀

    Also- This might be a little bit sloppy (but everything else in this thread is a bit sloppy thus far :-))

    declare @Inttime int

    set @Inttime = 133737

    declare @hour int

    declare @minute int

    declare @second int

    declare @time datetime

    set @time = '00:00:00'

    SET @hour = @Inttime / 10000

    SET @minute = ((@Inttime - @hour*10000) / 100)

    SET @second = ((@Inttime - @hour*10000) - @minute * 100)

    SET @time = DATEADD(hh, @hour, @time)

    SET @time = DATEADD(mi, @minute, @time)

    SET @time = DATEADD(s, @second, @time)

    select @hour, @minute, @second

    SELECT @time

    *EDIT - I accidentally posted the "test" of trying just 1, I also apparently misread the entire thread and was trying to do this on an int 😛

  • Here's an alternative query that uses a method similar to Mike McQueen's to parse the times from the time strings when converted to integers. This method does have the advantage that you don't need to worry about leading zeros. The query assumes that @time1 is earlier than @time2 so that if @time2 < @time1 then the times must span a (single) day boundary and amends the result accordingly.

    DECLARE @time1 int

    DECLARE @time2 int

    SELECT @time1 = CAST('235030' AS int), @time2 = CAST('21029' AS int)

    SELECT CONVERT(varchar(8), DATEADD(second,

    DATEDIFF(second,

    DATEADD(second, @time1 % 100, DATEADD(minute, (@time1 / 100) % 100, DATEADD(hour, (@time1 / 10000), 0))),

    DATEADD(second, @time2 % 100, DATEADD(minute, (@time2 / 100) % 100, DATEADD(hour, (@time2 / 10000), 0)))),

    CASE WHEN (@time2 < @time1) THEN 1 ELSE 0 END), 108)

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply