Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Modifying A Date So That It Is Neither A Weekend Or Holiday? Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:11 PM
Points: 6, Visits: 18
I have a stored procedure that is used to sync dates between two databases. It looks at the project start date in one database and the production date for a specific component in the other database. If that production date is later than the project start date, it syncs the two dates.

The stored procedure is run at midnight each night. Component production is set at 2:30pm each day. So, for example, at 2:30pm on July 1st, the production for July 2nd is set. Thus it doesn't do any good to have the stored procedure set production dates to July 1st or July 2nd and instead should use July 3rd as the next available start date.

This issue is addressed by checking if the intended start date is at least one day in the future and, if not, changing the intended start date to be such.

What I'm not entirely sure how to go about doing is checking to ensure that the new intended start date isn't a Saturday, Sunday or Holiday.

Checking if the date is a Saturday or Sunday is easy enough using DATENAME(dw, @STARTDATE) and then adding 1 or 2 days to the date accordingly.

What I'm not entirely sure about is the proper way to continue adding days to the date until it's neither a Saturday no Sunday and also doesn't appear in the Holidays table.

What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?
Post #1467758
Posted Wednesday, June 26, 2013 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
skempf (6/26/2013)
I have a stored procedure that is used to sync dates between two databases. It looks at the project start date in one database and the production date for a specific component in the other database. If that production date is later than the project start date, it syncs the two dates.

The stored procedure is run at midnight each night. Component production is set at 2:30pm each day. So, for example, at 2:30pm on July 1st, the production for July 2nd is set. Thus it doesn't do any good to have the stored procedure set production dates to July 1st or July 2nd and instead should use July 3rd as the next available start date.

This issue is addressed by checking if the intended start date is at least one day in the future and, if not, changing the intended start date to be such.

What I'm not entirely sure how to go about doing is checking to ensure that the new intended start date isn't a Saturday, Sunday or Holiday.

Checking if the date is a Saturday or Sunday is easy enough using DATENAME(dw, @STARTDATE) and then adding 1 or 2 days to the date accordingly.

What I'm not entirely sure about is the proper way to continue adding days to the date until it's neither a Saturday no Sunday and also doesn't appear in the Holidays table.

What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?


You should search this site for calendar tables. There are several great articles for this type of thing.


What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?


There is no proper structure for a loop for this type of thing. A loop is not needed if you use a calendar table and set based queries.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1467769
Posted Wednesday, June 26, 2013 10:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:50 AM
Points: 31,284, Visits: 15,749
I'd build a calendar tables (there are articles on this site for that). essentially populate it with all days, mark weekends, holidays, and other days. That way you can join with that table and determine the next available "work" day.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1467770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse