Select next business day

Experts,
Below is a simple select I use for setting shipping dates. If an order is processed before 14:30 it selects today, anfter 14:30 it selects tomorrow.. pretty straight forward.

I want to modify this to consider weekdays. meaning if an order is processed on a Friday after 14:30, it should select Monday not Saturday as the answer.  Using SQL server Mgmt Studio 2005


IF
(Select DATEPART(HOUR, CURRENT_TIMESTAMP) ) >= 14
and
(Select DATEPART(MINUTE, CURRENT_TIMESTAMP) ) > 30
Select DATEADD(DAY, 1, CURRENT_TIMESTAMP)
ELSE
SELECT CURRENT_TIMESTAMP

LVL 1
JDCamAsked:
Who is Participating?
 
Eric3141Connect With a Mentor Commented:
IF (Select DATEPART(HOUR, CURRENT_TIMESTAMP) ) >= 14 and
   (Select DATEPART(MINUTE, CURRENT_TIMESTAMP) ) > 30)
BEGIN
   if (select datepart(weekday, getdate()) ) = 1  -- Sunday
        Select DATEADD(DAY, 1, CURRENT_TIMESTAMP);

      if (select datepart(weekday, getdate()) ) = 7  -- Saturday
        Select DATEADD(DAY, 2, CURRENT_TIMESTAMP);

     if (select datepart(weekday, getdate()) ) NOT IN (1, 7)  -- Mon - Fri
        Select DATEADD(DAY, 1, CURRENT_TIMESTAMP);

END
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
create a calendar table:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

and you select the next day from that table that is not saturday, sunday, or eventually marked as holiday, and you are done.
yes, you "might" be able to solve this with "pure" sql (even for the moving holidays), but why take all that work if a simple calendar table can solve this at "no cost" and be 100% sure to be ok
0
 
JDCamAuthor Commented:
thanks Eric3141.
Had to modify a bit, but works fine


IF
      (select datepart(weekday, current_timestamp) ) = 7
SELECT DATEADD(DAY, 2, CURRENT_TIMESTAMP)
ELSE
IF
      (select datepart(weekday, current_timestamp) ) = 1
SELECT DATEADD(DAY, 1, CURRENT_TIMESTAMP)
ELSE
IF
      (Select DATEPART(HOUR, CURRENT_TIMESTAMP) ) >= 14
      and
    (Select DATEPART(MINUTE, CURRENT_TIMESTAMP) ) > 30
      and
      (select datepart(weekday, current_timestamp) ) = 6
SELECT DATEADD(DAY, 3, CURRENT_TIMESTAMP)
ELSE
IF
      (Select DATEPART(HOUR, CURRENT_TIMESTAMP) ) >= 14
      and
    (Select DATEPART(MINUTE, CURRENT_TIMESTAMP) ) > 30
      and
      (select datepart(weekday, current_timestamp) ) in (2,3,4,5)
SELECT DATEADD(DAY, 1, CURRENT_TIMESTAMP)
ELSE
SELECT CURRENT_TIMESTAMP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.