Query Previous Time Periods

ajetrumpet · Sep 21, 2008

With the following SQL statements, you can query previous time periods within your database (given the fact that you have a date field(s) in the table(s) that you are querying.

Current Week to Date

Code:

SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 

DateAdd("d", -((Weekday(Date()) - 1)), Date()) AND 

Date();

Current Month to Date

Code:

SELECT Table.DateField
FROM Table
WHERE (((Table.DateField) BETWEEN 

DatePart("m",Date()) & "/1/" & DatePart("yyyy",Date()) AND 

 Date()));

Current Year to Date

Code:

SELECT Table.DateField
FROM Table
WHERE (((Table.DateField) BETWEEN 

"1/1/" & DatePart("yyyy",Date()) AND 

Date()));

Previous Months

Code:

PARAMETERS [Number of Previous Months to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 

DateAdd("m", -[Number of Previous Months to Query], 
DatePart("m", Date()) & "/1/" & DatePart("yyyy", Date())) AND 

DateAdd("d", -1, 
DatePart("m", Date()) & "/1/" & DatePart("yyyy", Date()));

Previous Weeks (using "DAYS" parameter)

Code:

PARAMETERS [Number of Previous Weeks to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 

DateAdd("d", (-[Number of Previous Weeks to Query] * 7), 
DateAdd("d", -((Weekday(Date()) - 1)), Date())) AND 

DateAdd("d", (([Number of Previous Weeks to Query] * 7) - 1), 
DateAdd("d", (-[Number of Previous Weeks to Query] * 7), 
DateAdd("d", -((Weekday(Date()) - 1)), Date())));

Previous Weeks (using "WEEKS" parameter)

Code:

PARAMETERS [Number of Previous Weeks to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 

DateAdd("ww", -[Number of Previous Weeks to Query], 
DateAdd("d", -((Weekday(Date()) - 1)), Date())) AND 

DateAdd("ww", [Number of Previous Weeks to Query], 
DateAdd("ww", -[Number of Previous Weeks to Query], 
DateAdd("d", -(Weekday(Date())), Date())));

Previous Years

Code:

PARAMETERS [Number of Previous Years to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 

DateAdd("yyyy", -[Number of Previous Years to Query], 
"1/1/" & DatePart("yyyy", Date())) AND 

"12/31/" & DatePart("yyyy", DateAdd("yyyy", -1, Date()));

Brianwarnock · Jan 9, 2010

Adams fine work assumes the US date format, fair enough as Adam works in the States, and whilst most people will be able to adapt the code to other formats some may not, so I have taken the liberty of giving my versions. Being a DateSerial fan I have used that rather than Datepart, it has the added advantage of being format independent, returning the system default.

A couple of examples were not changed as they did not need to be.

Brian

Current Month to Date

Code:

SELECT Table.DateField
FROM Table
WHERE (((Table.DateField) BETWEEN
Dateserial(Year(Date()),Month(Date()),1) AND Date();

Current Year to Date

Code:

SELECT Table.DateField
FROM Table
WHERE (((Table.DateField) Between Dateserial(Year(Date()),1,1) And Date();

Previous Months

Code:

PARAMETERS [Number of Previous Months to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 
DateSerial(Year(Date()),Month(Date())-[Number of Previous Months to Query],1)
And DateSerial(Year(Date()),Month(Date()),0);

This pulls to the end of the previous month
So If we are in May and the number of previous months is 3 then we pull Feb to April.
Note that it does work over years you can quote 18 months for example

Previous Years

Code:

PARAMETERS [Number of Previous Years to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 
DateSerial(Year(Date())-[Number of Previous Years to Query],1,1) 
AND DateSerial(Year(Date())-1,12,31);

Query Previous Time Periods

ajetrumpet

Banned

Brianwarnock

Retired

Similar threads

Users who are viewing this thread