SQL Server: Round Date Up

user_618584 0
Viewed 5350

I want to round a datetime value up to the nearest full day.

UPDATE [DB].[dbo].[TABLE]
-- I'm unsure how to take the DepartureDate and round it up
SET DepartureDate = DepartureDate + ??
WHERE DATEPART(HOUR, DepartureDate) = 23

The DepartureDate is a datetime column.

So 2017-11-10 23:00:00.0000 becomes 2017:11:11 00:00:00.0000

sql-server sql database
3 Answers

Try this:

SELECT DepartureDate, 
       CASE WHEN DepartureDate = CONVERT(DATE, DepartureDate) 
            THEN DepartureDate 
            ELSE CONVERT(DATE,DepartureDate + 1)
       END RoundedDate
FROM YourTable;

This will round up DepartureDate to the next day, unless the DepartureDate is at exactly midnight already. It is leveraging the fact that a date type has a default time of midnight.

The update will simply be:

UPDATE YourTable
SET DepartureDate = CASE WHEN DepartureDate = CONVERT(DATE, DepartureDate) 
                         THEN DepartureDate 
                         ELSE CONVERT(DATE,DepartureDate + 1)
                    END;

Edit Since it was clarified in comments that you only want to do this for hour = 23, then just do this:

UPDATE YourTable
SET DepartureDate = CONVERT(DATE,DepartureDate +1)
WHERE DATEPART(HOUR, DepartureDate) = 23
user_54200080

Run this, to check the behviour rounds as you want, then apply to your query:

SELECT CAST(ROUND(CAST(getutcdate() as FLOAT),0) as datetime)

Recommend you SELECT your data with old date and new date, and eyeball it first to check.. Before you update

user_14106640

You are right in where clause, if u can use this script to add date

select DATEADD(DD, 1,DepartureDate )

--Update 

UPDATE [DB].[dbo].[TABLE]
SET DepartureDate = DATEADD(DD, 1,DepartureDate )
WHERE DATEPART(HOUR, DepartureDate) = 23
user_64841180
Related