CODEWITHSUNDEEP
sqlsql-servertimerounding
Orson DCunha
Orson DCunha

Reputation: 71

Rounding time to whole seconds in SQL Server 2008

I have a time column as 

Time
07:34:23.554
08:44:42.778
08:11:38.332

I want to round off the seconds field so that if value after decimal is less than 0.5 it gets rounded down and if the value after decimal is greater than 0.5 it gets rounded up.

My desired output is

Time
07:34:24
08:44:43
08:11:38

Any help is appreciated, Thanks.

Upvotes: 0

Views: 111

Answers (1)

Siyual
Siyual

Reputation: 16917

You can just cast it as TIME(0):

Declare @Time Table 
(   
    Time Time
) 

Insert  @Time 
        (Time)
Select  '07:34:23.554'  Union All 
Select  '08:44:42.778'  Union All 
Select  '08:11:38.332'

Select  Cast(Time as Time(0)) As RoundedTime, 
        Time 
From    @Time

Output:

RoundedTime Time
----------------------------
07:34:24    07:34:23.5540000
08:44:43    08:44:42.7780000
08:11:38    08:11:38.3320000

A more permanent solution would be to change the datatype of the column in the table itself to TIME(0):

Declare @Time Table 
(   
    Time Time(0)
) 

Insert  @Time 
        (Time)
Select  '07:34:23.554'  Union All 
Select  '08:44:42.778'  Union All 
Select  '08:11:38.332'

Select  *
From    @Time

Output:

Time
--------
07:34:24
08:44:43
08:11:38

Upvotes: 2

Related Questions