How to query a Date Column in SQL

Thread view:

Laura P - 18 Aug 2004 17:34 GMT

Hi,

I'm sorry if this is a very noddy question, but I seem to have a block
on how to do this...

I am in the process of refactoring a Java application which uses JDBC
to talk to Oracle and MySQL. The old way used to have a DateTime
field that was represented in the database as a text field, a typical
query on this field would be: "Select * from Table1 where DateTime <=
'2004/08/17 09:00:30'".

In the new database schema, the DateTime field is represented as a
Date field. My problem is that I have to parse my query text (which
is not SQL :( ) and create a String from that, which is valid SQL.
This happens in a different part of the application to the actual
database connection, so I cannot use a PreparedStatement and insert
the java.sql.Date value that way. I have found some information about
sql date operators such as DATEDIFF, but not enough to make use of
them, and I was not sure if these would work through the JDBC driver
even if I managed to get them working...

I would be very grateful for any information anyone has on this - I
can't believe it is as difficult as I have found it today to create a
data query SQL string!

Many thanks,

Laura

Reply to this Message

kaeli - 18 Aug 2004 18:29 GMT

> In the new database schema, the DateTime field is represented as a
> Date field. My problem is that I have to parse my query text (which
[quoted text clipped - 5 lines]
> them, and I was not sure if these would work through the JDBC driver
> even if I managed to get them working...

I'm not sure what you're trying to accomplish and what you can't get, but I
do direct queries for my own personal use without prepared statements to set
the date. I still technically prepare a statement, but it just goes straight
through.
Snippet:
String qs=request.getParameter("qs");
...
Statement s = conn.prepareStatement(qs);
ResultSet rs = s.executeQuery();

So, if I want to so something with my date field, I pass a direct query, such
as (oracle syntax)

select * from myTbl where myDateField=to_char('12/21/2004','MM/DD/YYYY')

If you wanted to get a date like that...

String qs;
java.util.Date myDate = new java.util.Date();
SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");

qs = "select * from myTbl where myDateField=to_char('"+
df.format(myDate)+
"','MM/DD/YYYY')";

Does that help?

Signature

--
~kaeli~
What's another word for thesaurus?
http://www.ipwebdesign.net/wildAtHeart
http://www.ipwebdesign.net/kaelisSpace

Reply to this Message

Lee Fesperman - 19 Aug 2004 00:25 GMT

> I am in the process of refactoring a Java application which uses JDBC
> to talk to Oracle and MySQL. The old way used to have a DateTime
[quoted text clipped - 15 lines]
> can't believe it is as difficult as I have found it today to create a
> data query SQL string!

Have you tried using the {d ...} escape sequence? It uses a standard date form that is
easy to create with DateFormat classes in java.text. Hopefully, both the Oracle and
MySQL JDBC drivers will support the escape sequence.

Signature

Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

Laura P - 19 Aug 2004 08:57 GMT

Thanks for the speedy answers!

I can't use to_char, as it is Oracle specific and I need to be able to
use the same sql for different DB providers.

I have looked up the escape sequence for dates and timestamps
(http://e-docs.bea.com/wls/docs81/jdbc_drivers/sqlescape.html), and it
looks like that should do what I need, although I have not tested it
yet.

Thank you both for your help :)

Laura

Reply to this Message

Don Vaillancourt - 19 Aug 2004 20:56 GMT

You have to use the TO_DATE function to tell Oracle what your date
format looks like.

For example:

select *
from employee
where hiring_date = to_date('08312004','MMDDYYYY')

> Thanks for the speedy answers!
>
[quoted text clipped - 9 lines]
>
> Laura

Signature

* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com

/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

Reply to this Message

How to query a Date Column in SQL

Free Magazines