Round down seconds from time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Round down seconds from time

Author

Topic

kev8279
Starting Member

6 Posts

Posted - 2012-05-25 : 11:12:10

Hi, I have a table which has a date and time in like this 11:01:39.01234

What I would like to do is round this down to 11:01, regardless of seconds. I have tried a cast to smalldate time however when the seconds are above 30 it rounds up. Can someone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-25 : 11:52:04

[code]
declare @dt datetime2 = '2012-05-26 11:01:39.01234'

select convert(datetime2, convert(varchar(10), @dt, 121) + ' ' + convert(varchar(5), @dt, 108))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-25 : 12:00:06

An alternative:

declare @dt datetime2 = '2012-05-26 11:01:39.01234'
SELECT DATEADD(minute,DATEDIFF(minute,0,@dt),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources