Help With Time Frames - SQL (1 Viewer)

[Shaunk23]

I have queries that are done by date range.. Below is my case statement to set the criteria... I need it to be checking for the current 30 days, current 90 , current 6 months & year. The problem im getting is it shows all the shipments when i select "30 days" all shipments are greater then that.. i need to have anything that is in the range 30 days - current / 90 - current etc.




Case "Current 30 Days": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 30))"
Case "Current 90 Days": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 90))"
Case "Current 6 Months": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 183))"
Case "Current Year": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 365))"
Case "All Shipments": SqlAge = "(((ExpBooking.[DateOfDeparture]) <> Date()-0))"

 

[jzwp22]

You will want to use the BETWEEN...AND in your WHERE clause; I also strongly recommend using the dateadd() function

This would pull records between 30 days ago and today:

Case "Current 30 Days": SqlAge = "ExpBooking.[DateOfDeparture] BETWEEN dateadd("d",-30,Date()) AND date()"


Case "Current 90 Days": SqlAge = "ExpBooking.[DateOfDeparture]BETWEEN dateadd("d",-90,Date()) AND date()"

etc.

For all shipments, you would not need a WHERE clause

 

[Shaunk23]

It throws an error on the "d"

 

[Shaunk23]

Got it.. needed to be 'D' Now what if i want to include records 30 days forward AND 30 days back?

 

[jzwp22]

My apologies, since you are doing this in code; they WHERE clauses should look like this:


For the 30 days:
SqlAge = "ExpBooking.[DateOfDeparture] BETWEEN #" & dateadd("d",-30,Date()) & "# AND #" & date() & "#"


For the 90 Days:

SqlAge = "ExpBooking.[DateOfDeparture] BETWEEN #" & dateadd("d",-90,Date()) & "# AND #" & date() & "#"

 

[jzwp22]

Got it.. needed to be 'D' Now what if i want to include records 30 days forward AND 30 days back?

Relative to today I assume



SqlAge = "ExpBooking.[DateOfDeparture] BETWEEN #" & dateadd("d",-30,Date()) & "# AND #" & dateadd("d",30, date()) & "#"

BTW, since the dates are variables they cannot be included between the double quotes but rather concatenated to the string. Dates have to be delimited by # signs.