Round a timestamp

Question

I would like to differentiate between two dates and round the result to the next day.

For exemple, if I have date1='2020-03-10 11:59:00' and date2='2020-03-10 20:53:00', the difference between date1 and date2 with datediff() is equal to 8 hours. I would like to round this result to have 24 hours.

EDIT

I tried by using dateadd() like this :

select DATEADD(HOUR, DATEDIFF(HOUR, '2020-03-10 11:59:00', '2020-03-10 20:53:00'),0)

Return 1900-01-02 09:00:00.000 doesn't correspond to what I want.

When you Google'd "SQL Server round datetime to next day", what did you find? — dfundako, Mar 12 '20 at 13:25
Does this answer your question? [T-SQL datetime rounded to nearest minute and nearest hours with using functions](https://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions) — Vijay, Mar 12 '20 at 13:27
This doesn't make any sense at all. How can you say the difference between those two date values is 8 hours??? They are 5 days apart. And ignoring the days and looking only at the time portion they are apart by 8 hours and 54 minutes. — Sean Lange, Mar 12 '20 at 13:28
@SeanLange I think they are in DD/MM/YY format. I'm not saying that saves this post, but I think thats where the OP is going. — dfundako, Mar 12 '20 at 13:32
Now you have changed the date string. One challenge here is your date literals are ambiguous. Is that October 3 and November 3 OR is that March 10 and March 11? — Sean Lange, Mar 12 '20 at 13:33
@dfundako they seem to be now but originally the values would have to have been MM-DD-YYYY. — Sean Lange, Mar 12 '20 at 13:34
With the new values, assuming this is March 10 and March 11 these two values are 33 hours apart, not 8. What should be the output from that? You need to provide a [mcve] if you want any real help here. — Sean Lange, Mar 12 '20 at 13:36
Just edited my question, now the two dates are March 10, sorry for ambiguity — MissKnacki, Mar 12 '20 at 13:40
OK so now they are the same day. The difference is 9 hours. Why should this round to 24? What is the logic you want to achieve here? — Sean Lange, Mar 12 '20 at 13:43
@Sean Lange it's to do an industrial calculation, for a given time interval that I have to round to upper day — MissKnacki, Mar 12 '20 at 13:47
That isn't providing the business rule. Why does 9 become 24? In other words why is it 24 and not 33? Or 74738949? — Sean Lange, Mar 12 '20 at 14:23
So if it crossed to another day it would need to be 48? You aren't doing a good job of explaining what you need here. Maybe it is as simple as using datediff with day and multiplying by 24? — Sean Lange, Mar 12 '20 at 14:47

score 1 · Accepted Answer · answered Mar 12 '20 at 14:51

The explanation of the output is not at all clear so I am just guessing here. I am taking the difference in days between two dates. Then adding 1 because if two dates are the same day the difference in days is 0. Then multiplying that result by 24. I changed up the date literal so it is ANSI compliant and will always get the correct date regardless of localization or language settings.

declare @date1 datetime = '20201003 11:59:00'
    , @date2 datetime = '20201003 20:53:00'

select datediff(day, @date1, @date2) + 1 * 24

It is working ! Thank you very much ! And sorry for the lack of clarity of my question, it's not very clear in my head ... — MissKnacki, Mar 12 '20 at 15:02

Round a timestamp

1 Answers1