Round to Hour - 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 2000 Forums

SQL Server Development (2000)

Round to Hour

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-12-14 : 09:12:31

Anyone have a easy method to round a datetime to the nearest hour?

Jim
Users <> Logic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-14 : 09:15:02

Same as rounding to any other interval:

SELECT DateAdd(hour, DateDiff(hour, 0, getdate()), 0)

The only intervals that don't work with that formula are seconds and milliseconds, because they will overflow an int value.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-12-14 : 10:05:46

Thanks Rob

I used it to create a table of half hour increments between two times
I just needed to start at an even hour.

Declare @TimeIn Datetime
Declare @Timeout Datetime

SELECT @TimeIn = DateAdd(hour, DateDiff(hour, 0, TimeIn), 0) ,
@Timeout = DateAdd(hour, DateDiff(hour, 0, Timeout), 0)
FROM TimeClockPlus.dbo.EmployeeHours
WHERE (RecordId = @RecordId)

Select DateAdd(mi,30, @TimeIn) As AvailIn
Into #AvailTime

Select @TimeIn = DateAdd(mi,60, @TimeIn)

While @TimeIn < @Timeout
Begin
Insert into #AvailTime
(AvailIn)
Select @TimeIn

Select @TimeIn = DateAdd(mi,30, @TimeIn)
end

Select AvailIn
From #AvailTime
Order by AvailIn

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources