Re: Comparing database dates: SELECT FROM WHERE Date(today) < MYDATECOL ?

From: Bjorn Abelli (DoNotSpam.bjorn_abelli_at_hotmail.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 23:02:48 +0100

"Thomas Stein" wrote...

> I want to get in a java prgm all those database records whose
> MYDATE column contains a date greater equal a given other date
> (say: today). How do I specify this in a SQL query from Java?
>
> Assume the comparison date in java is in a java.util.Date field. Do I have
> to convert it
> into a variable of type java.sql.Date?
>
> The resulting code should look similar to:
>
> java.util.Date somejavadate = new java.util.Date();
> java.sql.Date somesqldate = new java.sql.Date(somejavadate);
>
> rs = stmt.executeQuery("SELECT ... FROM ... WHERE " + somesqldate + "<=
> MYDATECOL");

This will fail in most cases, as that construction will make use of the
toString-method in java.sql.Date, which makes your querystring lokk
something like:

  SELECT ... FROM ... WHERE 2005-02-07 <= MYDATECOL

...which isn't a valid way to use dates...

You can in many databases use '' around the date:

  rs = stmt.executeQuery
     ("SELECT ... FROM ... WHERE '" + somesqldate + "' <= MYDATECOL");

...but, in many databases, such as MS Access, it won't work, as it demands
another formatting of a date literal.

> MYDATECOL is defined with a type "datetime".

So it's probably an SQL Server or MS Access/Jet... ;-)

> However this does not work.
> How does the exact query look like?

To make it less database specific, use a PreparedStatement, which is the
recommended way to deal with insertion of values into query strings (instead
of concatenations).

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

... and as you're using a DateTime type in the DB, you possibly want to make
use of Timestamp instead of java.sql.Date, as the latter truncates any time
information.

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Timestamp.html

   java.sql.Timestamp somesqldate =
      new java.sql.Timestamp(somejavadate.getTime());

   String sql =
      "SELECT ... FROM ... WHERE MYDATECOL >= ?";

   PreparedStatement stmt = con.prepareStatement(sql);

   ps.setTimestamp(1, somesqldate )

   rs = stmt.executeQuery();

> Is the date query database product specific?

Yes and no, as pointed out above.

By using a PreparedStatement, you can make your queries less db specific,
but there are still some db:s that won't map timestamps, dates, etc,
correctly. In those cases you will probably need to explore the specific
prepareStatement-class in the driver, i.e. the documentation for the
jdbc-driver from a specific vendor.

However, the example above will work for most db:s.

// Bjorn A

Relevant Pages

  • Re: PreparedStatement + "order by ?"
    ... Is there a way to use a PreparedStatement with a query such as ... The query seems to properly compile and execute, ... are not a general-purpose placeholder for whatever bit of SQL syntax you ... wouldn't be portable to most other databases. ...
    (comp.lang.java.programmer)
  • Re: SQL In 2 Databases
    ... >I was wondering how i could get my simple SQL query to check through 2 ... In maindata define a new View / Query which joins the real Table2 ...
    (microsoft.public.vb.general.discussion)
  • Re: Escape character weirdness
    ... The problem of course is that you can't return brackets from the ... Sql Server uses brackets '' to escape. ... I tried a 'LIKE ' query, ... PreparedStatement p = c.prepareStatement("insert into #foo ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Want help with database diagnostics tool design.
    ... Filter 2 Query ... what kind of databases you are trying to diagnose. ... any complicated n-tier solutions to this kind of application. ...
    (comp.lang.pascal.delphi.databases)
  • Re: Opening table - query running?
    ... That's actually the way relational databases, not just Access, ... unless you impose order by using a query with an order by clause. ... the rows will be returned in primary key sequence because Jet ...
    (microsoft.public.access.tablesdbdesign)