Post reply

Perform math on string

  • April 28, 2009 at 9:26 am

    #985221

    If you want to convert a string like that to a time, then I recommend this method:

    declare @Time int;

    select @Time = 123757;

    select dateadd(second, @Time%100, dateadd(minute, @Time%10000/100, dateadd(hour, @Time/10000, 0)))

    Just ran a speed test like this:

    create table #T (

    ID int identity primary key,

    TimeStr varchar(6));

    insert into #T (TimeStr)

    select top 1000000 abs(checksum(newid()))%240000

    from dbo.Numbers N1

    cross join dbo.Numbers N2;

    declare @Time datetime;

    set statistics time on;

    select @Time = dateadd(second, TimeStr%100, dateadd(minute, TimeStr%10000/100, dateadd(hour, TimeStr/10000, 0)))

    from #T;

    Took under a second (just about 900 milliseconds) to process 1-million rows. Very fast.

    If you want the hours, minutes, seconds thing from the original post, run this to get times, then use DateDiff(second) to get the seconds, minutes and hours, for the final output. You don't want to use DateDiff(hour), because you'll get 1 hour if the start time is 12:57 and the end time is 13:01, but if you use DateDiff(second)/3600, you'll get 0 hours, which is correct.

    - 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

  • April 28, 2009 at 10:29 pm

    #985637

    Jeff Moden (4/27/2009)

    I don't allow such garbage into my db's to begin with. 😛

    I believe this is the method that doesn't suck so much.

    :hehe:

    _____________
    Code for TallyGenerator

  • April 28, 2009 at 11:18 pm

    #985648

    Sergiy (4/28/2009)

    Jeff Moden (4/27/2009)

    I don't allow such garbage into my db's to begin with. 😛

    I believe this is the method that doesn't suck so much.

    :hehe:

    Heh... I just knew you were going to say that. 😀

    Now, if we can just teach Microsoft to fix it in SysJobsHistory, we'll be all set.: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)

  • April 29, 2009 at 12:22 am

    #985658

    GSquared (4/28/2009)

    If you want to convert a string like that to a time, then I recommend this method:

    declare @Time int;

    select @Time = 123757;

    select dateadd(second, @Time%100, dateadd(minute, @Time%10000/100, dateadd(hour, @Time/10000, 0)))

    Just ran a speed test like this:

    create table #T (

    ID int identity primary key,

    TimeStr varchar(6));

    insert into #T (TimeStr)

    select top 1000000 abs(checksum(newid()))%240000

    from dbo.Numbers N1

    cross join dbo.Numbers N2;

    declare @Time datetime;

    set statistics time on;

    select @Time = dateadd(second, TimeStr%100, dateadd(minute, TimeStr%10000/100, dateadd(hour, TimeStr/10000, 0)))

    from #T;

    Took under a second (just about 900 milliseconds) to process 1-million rows. Very fast.

    If you want the hours, minutes, seconds thing from the original post, run this to get times, then use DateDiff(second) to get the seconds, minutes and hours, for the final output. You don't want to use DateDiff(hour), because you'll get 1 hour if the start time is 12:57 and the end time is 13:01, but if you use DateDiff(second)/3600, you'll get 0 hours, which is correct.

    So far this is just the second best solution around....

    The best came from Jeff... Never allow such garbage in the DATABASE.. 😀

    Prevention is better than cure...

  • April 29, 2009 at 7:15 am

    #985886

    hayzer (4/29/2009)

    GSquared (4/28/2009)

    If you want to convert a string like that to a time, then I recommend this method:

    declare @Time int;

    select @Time = 123757;

    select dateadd(second, @Time%100, dateadd(minute, @Time%10000/100, dateadd(hour, @Time/10000, 0)))

    Just ran a speed test like this:

    create table #T (

    ID int identity primary key,

    TimeStr varchar(6));

    insert into #T (TimeStr)

    select top 1000000 abs(checksum(newid()))%240000

    from dbo.Numbers N1

    cross join dbo.Numbers N2;

    declare @Time datetime;

    set statistics time on;

    select @Time = dateadd(second, TimeStr%100, dateadd(minute, TimeStr%10000/100, dateadd(hour, TimeStr/10000, 0)))

    from #T;

    Took under a second (just about 900 milliseconds) to process 1-million rows. Very fast.

    If you want the hours, minutes, seconds thing from the original post, run this to get times, then use DateDiff(second) to get the seconds, minutes and hours, for the final output. You don't want to use DateDiff(hour), because you'll get 1 hour if the start time is 12:57 and the end time is 13:01, but if you use DateDiff(second)/3600, you'll get 0 hours, which is correct.

    So far this is just the second best solution around....

    The best came from Jeff... Never allow such garbage in the DATABASE.. 😀

    Prevention is better than cure...

    Absolutely, but that doesn't actually help the person with the original question, who was handed a database that already had this in it.

    - 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

  • April 29, 2009 at 7:32 am

    #985902

    Jeff Moden (4/28/2009)

    Sergiy (4/28/2009)

    Jeff Moden (4/27/2009)

    I don't allow such garbage into my db's to begin with. 😛

    I believe this is the method that doesn't suck so much.

    :hehe:

    Heh... I just knew you were going to say that. 😀

    Now, if we can just teach Microsoft to fix it in SysJobsHistory, we'll be all set.:hehe:

    Seems like they eventually did in SQL2K8. The run_date and run_time columns are both INTs. Which leads me to the question as to why they didn't eat their own dog food and made them DATE and TIME columns?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • April 29, 2009 at 7:47 am

    #985914

    Jan Van der Eecken (4/29/2009)

    Jeff Moden (4/28/2009)

    Sergiy (4/28/2009)

    Jeff Moden (4/27/2009)

    I don't allow such garbage into my db's to begin with. 😛

    I believe this is the method that doesn't suck so much.

    :hehe:

    Heh... I just knew you were going to say that. 😀

    Now, if we can just teach Microsoft to fix it in SysJobsHistory, we'll be all set.:hehe:

    Seems like they eventually did in SQL2K8. The run_date and run_time columns are both INTs. Which leads me to the question as to why they didn't eat their own dog food and made them DATE and TIME columns?

    Actually, they are already integers in SQL2K5 as well. Or am I perhaps looking at the wrong columns?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • April 29, 2009 at 8:29 am

    #985966

    You're looking at the right columns. They've been Int all along. It's a pain.

    - 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

  • April 29, 2009 at 8:42 am

    #985986

    Jan Van der Eecken (4/29/2009)

    Jeff Moden (4/28/2009)

    Sergiy (4/28/2009)

    Jeff Moden (4/27/2009)

    I don't allow such garbage into my db's to begin with. 😛

    I believe this is the method that doesn't suck so much.

    :hehe:

    Heh... I just knew you were going to say that. 😀

    Now, if we can just teach Microsoft to fix it in SysJobsHistory, we'll be all set.:hehe:

    Seems like they eventually did in SQL2K8. The run_date and run_time columns are both INTs. Which leads me to the question as to why they didn't eat their own dog food and made them DATE and TIME columns?

    My point exactly... as you said, they should be DATETIME, not INT.

    --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)

  • April 29, 2009 at 8:45 am

    #985991

    GSquared (4/29/2009)

    Absolutely, but that doesn't actually help the person with the original question, who was handed a database that already had this in it.

    Actually, it does... the person now knows that the developer working for him/her needs a healthy dose of high velocity pork chops. Since it's an in-house app, they have the opportunity to not only know how bad the developer made things, but they also have the possibility of making the necessary repairs sooner than later. 🙂

    --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)

  • April 29, 2009 at 8:47 am

    #985993

    hayzer (4/29/2009)

    So far this is just the second best solution around....

    Never allow such garbage in the DATABASE.. 😀

    Prevention is better than cure...

    Heh... a kindred spirit. I wish more people would embrace thoughts like those.

    --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)

  • April 29, 2009 at 4:19 pm

    #986319

    GSquared (4/29/2009)

    You're looking at the right columns. They've been Int all along. It's a pain.

    Sorry G, I must have misunderstood. Thought you meant earlier that the MS guys made them strings as well.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 12 posts - 16 through 26 (of 26 total)

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