SQL Server: convert to today then add 8 hours: part II

Question

In my older post, I tried to change the date to current date and add 8 hours time.

SQL Server: convert to today then add 8 hours

UPDATE business.dbo.db_schedule
SET nextUpdate= DATEADD(hh, 8,
                DATEADD(d, DATEDIFF(D,nextUpdate,Getdate()),
                          nextUpdate))
where sno=8

note: nextUpdate may be any time in the past.

if I want to go one step further; I want nextdate to be in the future time. Because if I add 8 hour once, the result may be still a time in the past, I may have to add one more (or even twice) so the result is in the future. Can i do it in one step?

Adam Wenger · Accepted Answer · 2012-10-02T06:15:39.977

3

UPDATE business.dbo.db_schedule
SET nextUpdate =
  DATEADD(HOUR
    , 8 * CEILING((DATEDIFF(MILLISECOND, nextUpdate, GETDATE()) / 3600000.) / 8.)
    , nextUpdate)
  )
WHERE sno = 8;

Instead of just adding 8 hours, we multiply 8 by the CELING of the number of hours apart nextUpdate is from GETDATE()

e.g. If nextUpdate is 2012-10-01 02:30:00 and GETDATE() is currently 2012-10-02 15:15:00, then there are 36.75 (rounded up to 37) Hours difference. The CEILING of 37 / 8 is 5, which means you will need to add 5 * 8 hours to get a time that is after GETDATE() yet retains the multiple of 8 hours from your current nextUpdate value.

SQLFiddle example of the solution

edited Oct 02 '12 at 06:15

answered Oct 02 '12 at 04:32

Adam Wenger

17,100
6
52
63

Nice logic. Not quite right though. "September, 26 2012 22:00", curtime=06:04:21 – RichardTheKiwi Oct 02 '12 at 06:06
@Richardakacyberkiwi Thank you for catching the edge case. I updated the query to work on Milliseconds instead of hours, which makes the values you supplied work as desired. – Adam Wenger Oct 02 '12 at 06:14

score 1 · Answer 2 · answered Oct 02 '12 at 06:21

I think this, although a lot to look at, will actually perform better, since it is just a bunch of calculations in a single pass through the data.

UPDATE business.dbo.db_schedule
SET nextUpdate=
  CASE
  WHEN
                DATEADD(hh, 8, DATEADD(d, DATEDIFF(
                D,nextUpdate,Getdate()), nextUpdate))
              > Getdate() THEN
                DATEADD(hh, 8, DATEADD(d, DATEDIFF(
                D,nextUpdate,Getdate()), nextUpdate))
  WHEN
                DATEADD(hh, 16, DATEADD(d, DATEDIFF(
                D,nextUpdate,Getdate()), nextUpdate))
              > Getdate() THEN
                DATEADD(hh, 16, DATEADD(d, DATEDIFF(
                D,nextUpdate,Getdate()), nextUpdate))
  ELSE
                DATEADD(d, 1+DATEDIFF(
                D,nextUpdate,Getdate()), nextUpdate)
  END
where sno=8

Note: the last one is simplified by adding 1 day into the inner expression instead of adding 24 hours at the outer.

Original idea below

The idea expands on the previous one, except that instead of adding just 8 hours, we add all 8, 16 and 24 as 3 separate rows, and use the GROUP BY/MIN to take the one with the earliest time after the current time.

update s
set nextUpdate = a.newTime
from business.dbo.db_schedule s
join
(
  select nextupdate, min(nextUpdate2) newTime
  from
  (
    SELECT nextupdate,
           nextUpdate2 = 
              DATEADD(hh, 8*x.y,
                  DATEADD(d,DATEDIFF(D,
                      d.nextUpdate,Getdate()),d.nextUpdate))
    from business.dbo.db_schedule d
    cross join (select 1 union all
                select 2 union all
                select 3) x(y)
  ) z
  where newTime > getdate()
  group by nextupdate
) a on a.nextUpdate = b.nextUpdate;

SQL Server: convert to today then add 8 hours: part II

2 Answers2