Post reply

Near Friday Date

  • November 5, 2012 at 4:52 pm

    #269674

    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

  • November 5, 2012 at 5:17 pm

    #1556253

    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 6, 2012 at 7:43 am

    #1556458

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 6, 2012 at 7:53 am

    #1556465

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 6, 2012 at 7:57 am

    #1556469

    Thanks a lot Jeff,

    it worked beautifully!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply