Post reply

Multi/Nested CONVERT's

  • October 20, 2009 at 4:47 pm

    #136698

    I was doing some conversions and was thinking there has to be a better way than what I'm doing.

    SELECT CONVERT(DATETIME,(CONVERT(CHAR(2),DATEPART(MONTH, DATEADD(MONTH, -1,GETDATE()))) + '/' + '16' + '/' + CONVERT(CHAR(4),DATEPART(YEAR, GETDATE())) + ' 00:00:01'))

    THANKS:w00t:

  • October 20, 2009 at 5:33 pm

    #1068607

    So the 16th of the previous month + 1s?

    SELECT DATEADD(s,1,DATEADD(d,15,DATEADD(m,-1,(DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))))

    Not a whole lot prettier, but no varchar conversions and it won't get screwed up in January like yours will ;-).

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 20, 2009 at 5:55 pm

    #1068615

    Heh... gettin' old... Seth beat me. Same idea though...

    SELECT DATEADD(ss,1,DATEADD(mm,DATEDIFF(mm,-16,GETDATE())-1,-16))

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

  • October 20, 2009 at 6:00 pm

    #1068618

    Jeff Moden (10/20/2009)

    Heh... gettin' old... Seth beat me. Same idea though...

    SELECT DATEADD(ss,1,DATEADD(mm,DATEDIFF(mm,-16,GETDATE())-1,-16))

    That's OK, yours was better. I was just in the car thinking about all the extra ADD's I had in there. Your age comes with wisdom! (Ok, and slowness, got nothin for that) 😉

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 20, 2009 at 7:42 pm

    #1068632

    Just for fun. 🙂

    select dateadd(ss,1296001,dateadd(month,datediff(month,0,getdate())-1,0))

    Classic first day of previous month plus seconds equal to 15*24*60*60+1.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • October 21, 2009 at 7:43 am

    #1068863

    Wow thanks guys!

Viewing 6 posts - 1 through 5 (of 5 total)

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