CODEWITHSUNDEEP
sqlms-accessdateadd
XCCH004
XCCH004

Reputation: 323

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())))

Upvotes: 0

Views: 200

Answers (3)

Gustav
Gustav

Reputation: 55816

You can de-obfuscate it a lot by using DateSerial:

where 
    po_issue_date > getDate() And 
    po_issue_date < DateSerial(Year(getDate()), Month(getDate()) + 8, 0)

Upvotes: 2

forpas
forpas

Reputation: 164089

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.

Upvotes: 1

SuperMaz
SuperMaz

Reputation: 11

I think the following:

  1. Take the current date
  2. Substract the current day of month -1 to get the first day of current month
  3. Add 8 month to this
  4. Substract 1 day to get the last day of the previous month

So it calculates some deadline in approx 8 months.

But I wonder how a PO issue date can be in the future...

Upvotes: 1

Related Questions