Rounding time seconds up in SQL Server

[ragstta]

Have two time values

1900-01-01 00:59:47.000 need to round up to
1900-01-01 01:00:00.000

1900-01-01 00:19:23.000 need to round down to
1900-01-01 00:19:00.000

Perferably using a function or something that does both on the same column!
Note the date above is the difference between as startdate and a enddate!

thanks
rag

[RhinoBull]

I think you'd have to write your own function to do that.

[brucevde]

Simply Converting/Casting datetime values to smalldatetime will perform the "rounding" you desire.

Declare @StartDate datetime

Set @StartDate = '1900-01-01 00:59:47.000'
Print Cast(@StartDate as smalldatetime)

Set @StartDate = '1900-01-01 00:19:29.000'
Print Cast(@StartDate as smalldatetime)

Jan 1 1900 1:00AM
Jan 1 1900 12:19AM