Specifying A Date Range Using SQL

[richlocus]

Hello:

Thank you for your assistance with Pass Through queries. I have increased the speed of an ODBC query of a table containing 3 million records from 55 minutes to 3 minutes filtering out all but 250,000 records for the final result.

I have another query that uses the same table of 3 million records, and I'm trying to also use Pass Through, but I'm having difficulty in setting up the correct SQL WHERE clause. I was able to do it in Access VBA but it takes forever, going through 3 million records.

There is a column of dates in the huge database.

I only want to use the latest 12 months of data, and skip all the rest of the 3 million records with dates outside of my required range of a little over 12 months.

The WHERE clause needs to find the MAX date in that of all the 3 million records. Using the MAX date, I need to subtract 12 months arriving at a "starting date" which will be about a year earlier. I will only select records >= the starting date. For my example, let's say the maximum date in those millions of records is September 17, 2022.

Here's a simplified example: I want to accept only the last 12 full months and any partial month after that... and bypass any older records. That range would be September 1, 2021 through September 17, 2022.

After working 12 hours straight, my mind is getting a bit foggy. RecordDate below refers the table's record date of each of the 3 million records.

I'm using a literal date just for this example (the date would actually come from a database record field):

This is an ACCESS VBA example. It has to be coded in SQL in a WHERE clause using SQL syntax.
Note: The first example is using MaxDate, the highest date on the file. SQL will need to find the max date and put it in the WHERE clause.

IN Access the query is: WHERE RecordDate >= DateSerial(Year(MaxDate) - 1, Month(MaxDate), 1)
Using MaxDate as 9/17/2022 with the query above, the answer is >= 9/1/2021

The second example shows a hardcoded date just for an example:
IN Access it is: WHERE RecordDate >= DateSerial(Year(CDate(#9/17/2022#)) - 1, Month(CDate(#9/17/2022#)), 1)

In the above example, the beginning date for the query to accomplish 12 months would be 9/1/2021.

Basically, I need the WHERE statement to be in Microsoft SQL.

Thanks!
Rich Locus

[HansV]

Try

WHERE RecordDate >= '2021-09-01'

[richlocus]

That Simple... Thanks Hans.

[HansV]

SQL Server expects dates as strings, enclosed in single quotes.