Post reply

Getting a datetime with minutes & seconds set to '00'

  • June 15, 2010 at 4:47 am

    #387155

    This might be really obvious but I cannot for the life of me figure out how to get a datetime value with the minutes & seconds set to zero.

    ie if I have a time of '2010-04-04 14:15:02'

    I want '2010-04-04 14:00:00'

    so I'm looking to round my time down to the start of the hour given - can anyone help please I'm sure I'm missing something simple!

    Thanks

  • June 15, 2010 at 4:50 am

    #1180303

    SELECT dateadd(hour, datepart(hour,GETDATE()),CAST(convert(varchar(20),getdate(),112) as datetime))

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • June 15, 2010 at 4:52 am

    #1180305

    Good try but that sets the whole time part to zeros.

  • June 15, 2010 at 4:53 am

    #1180307

    Sorry, I have edited the previous post as well:

    SELECT dateadd(hour, datepart(hour,GETDATE()),CAST(convert(varchar(20),getdate(),112) as datetime))

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • June 15, 2010 at 4:56 am

    #1180309

    Spot on, thank you for helping.

  • June 15, 2010 at 11:56 am

    #1180612

    This is slightly more efficient:

    SELECT DATEADD(hh, DATEDIFF(hh,0,'2010-04-04 14:15:02'), 0)

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 15, 2010 at 12:33 pm

    #1180631

    Much appreciated

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

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