between two dates - one of the dates is today-1

[get2guy]

Hi,

I need a query criteria which is between two dates.

The first is fixed, example #01/07/2024# and the other is TODAY-1

Today is the 27/8/2024 so I need the values to be between 01/07/2024 and 26/8/2024



Thanks in advanced to any reply

[Welshgasman]

Use Date - 1 with Between
If you are going to hardcode dates then they need to be in mm/dd/yyyy or yyyy-mm-dd format.

[CJ_London]

In sql 01/07/2024 is interpreted as 7th Jan

you need to format your date as mm/dd/yyyy or the sql standard of yyyy-mm-dd

31/8/2024 is ok because there are not 31 months in a year

[ArviLaanemets]

Or avoid any possible date format issues, and use 'BETWEEN DateSerial(2024,7,1) AND Date()-1'

[get2guy]

Works! thanks!

Or avoid any possible date format issues, and use 'BETWEEN DateSerial(2024,7,1) AND Date()-1'

[abuislam]

To filter values between a fixed date and TODAY-1, use this SQL query:sqlCopy codeSELECT *FROM your_tableWHERE date_column BETWEEN '2024-07-01' AND DATEADD(day, -1, GETDATE());

[Welshgasman]

To filter values between a fixed date and TODAY-1, use this SQL query:sqlCopy codeSELECT *FROM your_tableWHERE date_column BETWEEN '2024-07-01' AND DATEADD(day, -1, GETDATE());

I seriously doubt that will work?
In fact I tested it to be sure and got data type mismatch, which I would have expected.

[CJ_London]

That is T-SQL syntax, not access sql syntax