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

Question

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

Balance

SSCrazy

Points: 2636
More actions
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

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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]

Balance SSCrazy Points: 2636 More actions · Answer 2

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

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]