How can I set a datetime after 4:00 or on weekend to 8:00am the next business day?

Question

I am trying to make this a trigger, but so fat this is all I've got:

CREATE TRIGGER trg_SetDateToNextBusinessDay
ON dbo.Orders
AFTER INSERT
AS
    UPDATE dbo.Orders
    SET OrderDateTime = {next  day that is Monday-Friday, 8:00 am}
    WHERE OrderDateTime {is after 4:00} OR OrderDateTime {is on a saturday or sunday}

What is the easiest way to do this?

Then switch on that value: http://stackoverflow.com/q/5487892/153923 — jp2code, Jun 05 '14 at 20:06
What about bank holiday? Should they be skipped? In that case a calendar is needed — Serpiton, Jun 05 '14 at 20:08
@Serpiton At this point in time, holidays are not being taken into account. Only day of the week. — drewwyatt, Jun 05 '14 at 20:08
Meh! Sorry man. I tried doing this, but my SQL isn't so good. My scratch is getting long and nasty looking. — jp2code, Jun 05 '14 at 20:25

score 4 · Accepted Answer · answered Jun 05 '14 at 20:37

Try this:

CREATE TRIGGER trg_SetDateToNextBusinessDay
ON dbo.Orders
AFTER INSERT
AS
UPDATE dbo.Orders
    SET OrderDateTime = 
                    (CASE DATENAME(DW, OrderDateTime)
                        WHEN 'Friday' THEN DATEADD(DAY, DATEDIFF(DAY, -3, OrderDateTime), '08:00:00') -- if friday, add 3 days
                        WHEN 'Saturday' THEN DATEADD(DAY, DATEDIFF(DAY, -2, OrderDateTime), '08:00:00') -- if saturday add 2 days
                        ELSE DATEADD(DAY, DATEDIFF(DAY, -1, OrderDateTime), '08:00:00') -- on all other days just add one day
                    END) -- {next  day that is Monday-Friday, 8:00 am}
    WHERE DATEPART(HH, OrderDateTime) > 3 OR-- {is after 4:00} (this includes 4:00!)
            DATENAME(DW, OrderDateTime) IN ('Saturday', 'Sunday') -- {is on a saturday or sunday}

I've never seen the `DATEDIFF` with negative starting value, good trick +1 — Serpiton, Jun 05 '14 at 21:35

score 1 · Answer 2 · answered Jun 05 '14 at 20:28

To check if the date is a Saturday or Sunady I suggest datename.

You can check:

datename("dw", OrderDateTime) in ("Saturday", "Sunday").

You can use datepart to get the hour and check if it is after four.

datepart(hh, OrderDateTime) >= 4

For your set statement you can use a case:

case datename("dw", OrderDateTime) 
when "Saturday" 
then DATEADD(hh, 56,cast(OrderDateTime As Date)) 
when "Friday"
then DATEADD(hh, 80,cast(OrderDateTime As Date))
else DATEADD(hh, 32,cast(OrderDateTime As Date))

In all cases we are getting rid of the time. Then, if it is Saturday, add two days worth of hours to get us to Monday and add an additional eight hours to get us to eight am.

If it is Friday, add three days plus eight hours. Otherwise add a single day plus eight hours.

How can I set a datetime after 4:00 or on weekend to 8:00am the next business day?

2 Answers2