CODEWITHSUNDEEP
sqlms-accessbetween
André
André

Reputation: 81

BETWEEN two dates with functions SQL

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

Upvotes: 3

Views: 5833

Answers (2)

HansUp
HansUp

Reputation: 97111

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

Upvotes: 3

Darren Kopp
Darren Kopp

Reputation: 77637

It looks like you are attempting to make a date by just putting the numbers in a date format like 'yyyy-mm-dd', but really you are just subtracting a bunch of numbers (2013-3-5 = 2005) and so I'm guessing you are getting a type mismatch.

I think MS Access has a DATEADD function where you can build a date through arithmetic.

If you want since the 20th of last month it would be something like

  1. Add -1 * current day number (subtract off days in current month)
  2. Add -1 months (to get last month)
  3. Add 20 days (get the 20th)

I think it would be something like this

DATEADD(d, 20, DATEADD(m, -1 * Month(Date()), DATEADD(d, -1 * Day(Date()), Date())))

This can be done with subsequent DATEADD calls

Upvotes: 0

Related Questions