• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Date

 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Since its not an SQL Date, yes, you will need to use Java to test the date.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34641
363
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic