Search code examples
sqlms-accessms-access-2016

MS Access SQL - How to pull last 13 months data from the first of the first month?

I tried the below code to pull last 13 months of data. But it is not pulling from the 1st of the month. How can I modify it to pull data from the first day of the month? For e.g. If I am running it today (11/13/18), I want data from 11/1/17 to today.

    SELECT * FROM MyTable T 
    WHERE T.[Date Joined] >= DATEADD("m", -13, DATE());
Solution

  • You need to change the day to 1 as

    SELECT * 
FROM MyTable T 
WHERE T.[Date Joined] >= DATEADD("d", -(DAY(Date())-1),DATEADD("m", -13, DATE());

    Or by using DATESERIAL() function which return a DATE from parts as

    SELECT * 
FROM MyTable T 
WHERE T.[Date Joined] >= DATEADD("m", -13, DATESERIAL(YEAR(Date()), MONTH(DATE()), 1))

    It will get the YEAR part from the date which is 2018 in your case, then get the MONTH part which is 11 and 1 which is the day and concatenate them to return a DATE as 2018-11-1