InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:45:29 GMT20http://www.sqlservercentral.com/Forums/Topic1381316-392-1.aspxI would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7, 7= Sunday) day of the week.You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.WHERE sale_day LIKE '2012W26-[67]'There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html But what is the business rule for a three-day weekend (Good Friday, for example)? And you want to get to the coming Friday or the prior Friday if it is in the future? Tue, 06 Nov 2012 14:24:53 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1381316-392-1.aspxThanks a lot Jeff,it worked beautifully!!!Tue, 06 Nov 2012 07:57:00 GMTMaiquehttp://www.sqlservercentral.com/Forums/Topic1381316-392-1.aspxIf what I state in the previous post is true, the following will do the trick. Here's a test.[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier. IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;GO--===== Populate the table with test data. -- This is not a part of the solution. SELECT TOP 100 DueDate = ABS(CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME), PayDate = CAST(NULL AS DATETIME) INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2; --===== This calculates the pay-on date for each date -- and stores it in the same table. -- This is a solution that doesn't depend on DATEFIRST. UPDATE #TestTable SET PayDate = DATEADD(dd,DATEDIFF(dd,4,DueDate+6)/7*7,4);--===== Display the results for verification. -- This is not a part of the solution. SELECT DueDate, DATENAME(dw,DueDate), PayDate, DATENAME(dw,PayDate) FROM #TestTable;[/code]Beware, though. The requirements you've given absolutely guarantee that every bill will be paid late. Not a good way to make your vendors happy.Tue, 06 Nov 2012 07:52:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1381316-392-1.aspx[quote][b]Maique (11/5/2012)[/b][hr]Our Acounts Payable changed the way they pay vendors, from now on they will cut checks only on fridays, I was able to calculate the invoice due date based on the vendor terms and invoice date, now for example if the due date is on '11/20/2012' - Tuesday- I have to show a new column 'To be paid on' -'11/23/2012'-.Any suggestions?Thanks[/quote]If the due date is on a Friday, do you want the pay-on date to be that Friday or the following Friday?Tue, 06 Nov 2012 07:43:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1381316-392-1.aspxAssuming you have DATEFIRST set to 1, change GETDATE() to your date value.[code="sql"]DATEADD( dd, 5 - DATEPART( dw, GETDATE()), GETDATE())[/code]Mon, 05 Nov 2012 17:17:18 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381316-392-1.aspxOur Acounts Payable changed the way they pay vendors, from now on they will cut checks only on fridays, I was able to calculate the invoice due date based on the vendor terms and invoice date, now for example if the due date is on '11/20/2012' - Tuesday- I have to show a new column 'To be paid on' -'11/23/2012'-.Any suggestions?ThanksMon, 05 Nov 2012 16:52:51 GMTMaique