Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2007
    Posts
    29

    Unanswered: Setting a date in the future

    I have had one tossed at me that I've never dealt with before. While I've done calculations on date parts I'm a little lost on how to proceed. They want a stored procedure that will take date from one field and add three days to it and that would update another field. The three days must account for weekend and holidays.

    The date would be receiveddate 12/03/2007 and the due date 12/06/2007. If someone has run into this and has some sample code it would be a great help.

    Any help would be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "must account for weekend and holidays" requires either

    1) a calendar table in which these are flagged somehow
    2) a user-defined function or stored procedure which algorithmically knows your particular set of holidays

    from the perspective of the client or user, 1 is a better choice because in most organizations, it's easier to admin the data than to request a programming change from the IT department
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    30
    Maybe I'm missing something, but isn't this what the DATEADD function is for?

  4. #4
    Join Date
    Aug 2007
    Posts
    29
    I was thinking about using a table with the dates and using a 1 or 0 to indicate a holiday or not.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dadyswat
    I was thinking about using a table with the dates and using a 1 or 0 to indicate a holiday or not.
    yes!!

    might as well indicate weekends, too, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2007
    Posts
    29
    Yes, if the date falls on a weekend then we would use the following Monday or if it was Thursday then Tuesday would be the 3rd day. I've got the field updating OK on the plus 3 days but now I have to account for the weekends and holidays.

  7. #7
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Try something like this:

    Code:
    -- SET DATEFIRST to U.S. English default value of 7.
    --Sunday 1
    --Monday 2
    --Tuesday 3
    --Wednesday 4
    --Thursday 5
    --Friday 6
    --Saturday 7
    
    SET DATEFIRST 7
    GO
    
    declare @num int
    
    select @num = DATEPART(dw, getdate()+3)
    
    if @num in (1, 7)
    	BEGIN
    		print 'Original due date is a weekend: ' + cast(@num as varchar(2))
    		if @num = 7
    			print 'New due date is Monday'
    			--check for holiday
    		else
    			print 'New due date is Tuesday'
    			--check for holiday
    	END
    else
    	print 'Due date is during the week: ' + cast(@num as varchar(2))
    	--check for holiday
    Hope this helps

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    reghardt, what about thanksgiving weekend? christmas? easter?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    This sounds mighty familiar to this Access thread: http://www.dbforums.com/showthread.php?t=1624506

    The code I posted is VBA, but the logic would be the same in T-Sql.
    Like r937 says, the table with the holidays would have to be maintained because some holidays fall on different dates each year. If a holiday falls on a weekend, you might get a day-off the prior Friday or the following Monday.
    Don't Bogart That Squishee!

  10. #10
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    You don't suppose this could be a school assignment, eh? Or maybe dadyswat is really elizabeth1 in disguise?
    Don't Bogart That Squishee!

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by a-dam
    You don't suppose this could be a school assignment, eh? Or maybe dadyswat is really elizabeth1 in disguise?
    no, that's unlikely, and no, they aren't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is anyone else concerned about the transient dependency or is it just li' ol' me?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    must be just you

    here in north america they are called "indigents" not "transients" and we have social services to deal with their dependencies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you in North America?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes sorry Rudy - transitive.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •