File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Date" Watch "Date" New topic
Author

Date

Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
MySql 3.23.54

I have a column in my table which contains the date but it's stored as a varchar. Unfortunately, that's how I receive it.

The format is mm/dd/yyyy (eg 03/15/2005)

What I'm looking to do is check to see if this date is less than one year old.

if (currentDate) < (03/15/2006) { good date } else { expired }

Should I grab the value from the date column and try to convert it to a java.util.date object and then do the comparison in Java or is there a way to do this comparison in mysql without the extra coding?

Thanks,

Drew
M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
Since its not an SQL Date, yes, you will need to use Java to test the date.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Isn't there a way to convert a string to a date in mySQL? Can't mySQL give you the current date? Can't mySQL do date arithmetic?

I know that both Informix and Oracle could accomplish all those tasks easily.

[You get what you pay for. ]

Good Luck,
Avi.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30753
    
156

Although sometimes you get what you don't pay for

Drew,
Take a look at the mySql function list. STR_TO_DATE(str,format) will convert your field to a date which you can then do arithmetic with.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Originally posted by Jeanne Boyarsky:
Although sometimes you get what you don't pay for

Drew,
Take a look at the mySql function list. STR_TO_DATE(str,format) will convert your field to a date which you can then do arithmetic with.


Very cool, but not available until MySQL 4.1.1

I guess I should upgrade soon, but hey it's working and I don't like to fix things that aren't broken.

I was able to get it to work in Java just fine although there is probably an extra performance hit for doing it this way...

Drew
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Yes, a quick look at the mySQL documentation shows that it has all these capabilities. Took only a few seconds to discover, too.

Good Luck,
Avi.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Date