SQL Query Current Date Minus 180 days?

Question

Query I'm getting a syntax error:

SELECT "LastFilterDate" 
FROM "Filter Cartridge Updates" 
WHERE "Filter Cartridge Updates"."LastFilterDate" < DATE_SUB(CURDATE(),INTERVAL 180 DAY)

I want to select the LastFilterDateS that are older than the current date minus 180 days (6 months).

I'm using LibreOffice base.

error in question:

SQL Status: HY000 Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Sandesh gave a fantastic link on the subject, but I'm still getting a syntax error. variations I've tried:

SELECT * 
FROM Filter Cartridge Updates 
WHERE LastFilterDate BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 180 DAY ) ) AND DATE ( NOW() )


SELECT * 
FROM "Filter Cartridge Updates" 
WHERE "Filter Cartridge Updates"."LastFilterDate" BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 180 DAY ) ) AND DATE ( NOW() )

Oh dear, I should have mentioned: When I run this in LibreOffice Base I get a syntax error. — , Oct 18 '14 at 09:23
http://stackoverflow.com/questions/15633653/mysql-date-subnow-interval-1-day-24-hours-or-weekday — Sandesh, Oct 18 '14 at 09:29
Not sure what DBMS engine is behind LibreOffice, but in standard SQL you would use `current_date - interval '180' day` — , Oct 18 '14 at 09:36

score 0 · Answer 1 · answered Oct 18 '14 at 12:16

You can find some help on the LibreOffice website. I found this: =DATEDIF("1974-04-17";"2012-06-13";"d") which yields the number of days between 2 dates.

So you could change your where clause to be (if this allows substitution - you'll have to test that), to:

Where =DATEDIF(last_filter_date,current_date;"d") > 180

Note: I only put stubs in for the dates, you'll have to format those correctly.

score 0 · Accepted Answer · answered Jul 17 '15 at 09:30

0

The problem is that you're using MYSQL syntax for an SQL that is not MYSQL.

answered Jul 17 '15 at 09:30

veta

716
9
22

SQL Query Current Date Minus 180 days?

2 Answers2