BETWEEN two dates with functions SQL

between, ms-access, sql

Solution

You can use `DateAdd` to subtract one month from today's date. Here's an example from the Immediate window.

? Date()
8/15/2013 
? DateAdd("m", -1, Date())
7/15/2013

Then you can determine the `Year` and `Month` of that previous date.

? Year(DateAdd("m", -1, Date()))
 2013 
? Month(DateAdd("m", -1, Date()))
 7

So finally you can give `DateSerial` the `Year`, `Month`, and 20 as the day.

? DateSerial(Year(DateAdd("m", -1, Date())), _
    Month(DateAdd("m", -1, Date())), 20)
7/20/2013

In a query, try it like this ...

WHERE [dtUpdated] BETWEEN
    DateSerial(
        Year(DateAdd("m", -1, Date())),
        Month(DateAdd("m", -1, Date())),
        20)
    AND Date()

Problem

I would like to extract data from the 20th last month until now, but is it impossible to make functions when making a BETWEEN AND command? ``` WHERE ([dtUpdated] BETWEEN ((Year(Date()))-(Month(Date())-1)-20) AND (Date())) ```

Original source