Using datediff in sql jdbc query

Question

I'm attempting to create a JDBC query with the following statement

String query = "SELECT COLUMN1,DATECOLUMN2 FROM tableName +
            "where datediff(d,DATECOLUMN2,getdate()) <= 1";
st = conn1.createStatement();
rs = st.executeQuery(query);  //receiving error here

I am receiving the following error message

java.sql.SQLException: "d" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

I'm sure the query isn't recognizing the datediff function for some reason I am not sure why since i was previously using HQL in the same application and retrieving the values.

In an attempt to use an alternative function I used

{fn TIMESTAMPADD( SQL_TSI_DAY, 1, CURRENT_TIMESTAMP)}

but it also failed I later on found that this is only used for Derby Database's

Can someone assist me in using the proper sql function to compare a date with the current date using JDBC

FWIW, the query you posted works fine in sql server (after properly closing the quotes). That suggest the problem is in some other part of the query that we cannot see. Or you are using a different database type ;-) — Leigh, Jan 10 '13 at 04:32

score 3 · Accepted Answer · answered Jan 10 '13 at 03:17

3

String query = "SELECT COLUMN1,DATECOLUMN2 FROM tableName "+
               "where datediff(day,DATECOLUMN2,getdate()) <= 1";

answered Jan 10 '13 at 03:17

John Woo

258,903
69
498
492

Thanks for the response. I've tried that as well as 'dd' also in all caps didn't work – Marquis Blount Jan 10 '13 at 03:31
@MarquisBlount: Did you merely try replacing `d` with `dd` or `day`? I guess the real issue is the actual position of the double quote after `tablename`. I suspect you didn't leave a space at the end of the string and, as a result, the table name merged with `where`. Please see my answer for details. – Andriy M Jan 10 '13 at 09:36

score 0 · Answer 2 · edited Jan 10 '13 at 04:32

0

You have a comma before from. Based on the error messages you are running this against SQL server.

String query = "SELECT COLUMN1,DATECOLUMN2 FROM tableName " 
              +" where datediff(d,DATECOLUMN2,getdate()) <= 1";

edited Jan 10 '13 at 04:32

Leigh

28,765
10
55
103

answered Jan 10 '13 at 03:32

user1965397

1

sorry that came from me editing the query and removing additional fields that were irrelevant good catch though – Marquis Blount Jan 10 '13 at 03:35

Bohemian · Answer 3 · 2013-01-10T04:56:35.857

0

The comma after the "d" should be a dot:

where datediff(d.DATECOLUMN2,getdate())
--------------- ^ dot here

edited Jan 10 '13 at 04:56

answered Jan 10 '13 at 03:54

Bohemian

412,405
93
575
722

score 0 · Answer 4 · answered Jan 10 '13 at 09:29

The posted snippet doesn't have a closing double quote between tableName and +, but I figure that is just a typo. However, in your real code, where precisely is the double quote? Is it directly after tablename, like this

String query = "SELECT COLUMN1,DATECOLUMN2 FROM tableName" +

or after the space that follows tablename, like this

String query = "SELECT COLUMN1,DATECOLUMN2 FROM tableName "+

It is very likely the former, because in that case the resulting query would look exactly the way as to cause the error you are getting. Take a look at this:

SELECT COLUMN1,DATECOLUMN2 FROM tableNamewhere datediff(d,DATECOLUMN2,getdate()) <= 1

You can see that where merges with the table name and datediff becomes an alias. What follows is interpreted as table hints. (You can specify table hints without WITH in older versions of SQL Server/older compatibility levels.) Consequently, SQL Server stumbles over d, as that is indeed an incorrect table hint.

Using datediff in sql jdbc query

4 Answers4