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

Near Friday Date Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 4:52 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 7, 2015 11:59 AM
Points: 69, Visits: 263


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
Post #1381316
Posted Monday, November 5, 2012 5:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 5,038, Visits: 11,567
Assuming you have DATEFIRST set to 1, change GETDATE() to your date value.

DATEADD( dd, 5 - DATEPART( dw, GETDATE()), GETDATE())




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


Forum Etiquette: How to post data/code on a forum to get the best help
Post #1381323
Posted Tuesday, November 6, 2012 7:43 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 37,997, Visits: 34,907
Maique (11/5/2012)


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

If the due date is on a Friday, do you want the pay-on date to be that Friday or the following Friday?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381570
Posted Tuesday, November 6, 2012 7:52 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 37,997, Visits: 34,907
If what I state in the previous post is true, the following will do the trick. Here's a test.
--===== 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
;

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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381579
Posted Tuesday, November 6, 2012 7:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 7, 2015 11:59 AM
Points: 69, Visits: 263
Thanks a lot Jeff,
it worked beautifully!!!
Post #1381583
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse