Search code examples
sqlms-accessdateadd

How to decipher complex DATEADD function from MS Access

I have inherited a query from an old MS Access DB and cannot for the life of me figure out what was trying to be done in this date parameter function. I normally only use SQL and this seems a bit different. Can any one assist in describing what this logic is doing? 

use pdx_sap_user 
go

select po_number, 
       po_issue_date 

from   vw_po_header 

where po_issue_date > getDate() And PO_issue_date < DateAdd("d",-1,DateAdd("m",8,DateAdd("d",-(Day(getDate())-1),getDate())))
Solution

  • First: there is no getDate() function in Access. Probably it should be Date() which returns the current date.
    Now starting from the inner expression:
    Day(Date()) returns the current day as an integer 1-31.
    So in DateAdd("d", -(Day(Date())-1), Date()) from the current date are subtracted as many days as needed to return the 1st of the current month.

    Then:

    DateAdd("m", 8, DateAdd("d", -(Day(Date())-1), Date()))

    adds 8 months to the the 1st of the current month returning the 1st of the month of the date after 8 months.
    Finally:

    DateAdd("d", -1,...)

    subtracts 1 day from the date returned by the previous expression, returning the last day of the previous month of that date.
    So if you run today 13-Sep-2019 this code, the result will be:

    30-Apr-2020

    because this is the last day of the previous month after 8 months.