Pulling records based on Date in JDBC

Question

How can I return all customers whose last appointment_date was 11 months ago from the date today? Or 12 months ago from the date in a month from today?

The first statement is working where I get the appointment from_date and compare it to the current date and return all the appointments that are happening tomorrow:

SELECT  appointment.id, 
        appointment.from_date 
WHERE   (julianday('now') - julianday(appointment.from_date)) = 1

But for the second statement I cant figure out how to return all customers whose last appointment date was 11 months ago from the current date?

SELECT  customer.id, 
        customer.last_appointment_date
FROM    customer 
WHERE   datediff(month, customer.last_appointment_date, DATEADD(month, getDate())) = 12

datediff() doesn't work because I am using SQLite and it is not a recognised function.

Any help would be greatly appreciated.

EDIT: I am running these query's in my code in netbeans i am using the sqllitejdbc driver to run them through prepared statements

I have edited, its because i am running through netbeans, everytime i use datediff(month, customer.last_appointment_date, DATEADD(month, getDate())) = 12 it returns month not a valid column - it doesnt recognise it as a valid date part?

returned: Caused by: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: month)

I see syntax errors in both those statements. Are you sure this is the sql you are using? What DBMS are you using? — crthompson, Mar 26 '15 at 14:54
try to remove comma after customer.last_appointment_date in the second query. Datediff is supposed to work with jdbc — Dmitrij Kultasev, Mar 26 '15 at 14:57
"*datediff() doesn't work because I am using JDBC*" doesn't make sense. JDBC doesn't change your SQL. So if the function is valid for your DBMS you _can_ use it through JDBC. — , Mar 26 '15 at 14:59
I have edited, its because i am running through netbeans, everytime i use datediff(month, customer.last_appointment_date, DATEADD(month, getDate())) = 12 it returns month not a valid column - it doesnt recognise it as a valid date part? returned: Caused by: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: month) — fooll, Mar 26 '15 at 15:01
That isn't the proper syntax for `DATEADD`. By the sound of your question you should only want `datediff(month, customer.last_appointment_date, getdate())` — Siyual, Mar 26 '15 at 15:08

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

Calculate the date to compare your field with, instead of calculating the difference and comparing to a constant, that way the database can make use of an index to locate records.

Use the date function instead of dateadd (see SQLite equivalent of SQL Server DateAdd function):

SELECT  customer.id, 
        customer.last_appointment_date
FROM    customer 
WHERE   customer.last_appointment_date = date('now', '-11 month')

edited May 23 '17 at 12:06

Community

1
1

answered Mar 26 '15 at 15:07

Guffa

687,336
108
737
1,005

i tried this however it only works if my last_appointment_date is of type DATETIME but is entered as a string in form 2015-02-01 and cannot return dates that are in sqldate format? – fooll Mar 27 '15 at 17:45
1

@fooll: SQLite doesn't have any `datetime` data type. If the date is stored in a format that SQLite doesn't understand, you would need to convert it. See https://www.sqlite.org/lang_datefunc.html – Guffa Mar 27 '15 at 19:30

Pulling records based on Date in JDBC

1 Answers1