Need rows with certain dates

[subnet_rx]

I have rows in a db stored with dates in the form of: 2007-07-07 00:00:00 (type is text).  I need to get all dates between now and 14 days from now.  Can I get some help on how I should do this?  Since the type is text, do I have to use strlen?  Or can I do this all in the query somehow?   

[GingerRobot]

How about the query:

 

SELECT * FROM `yourtable` WHERE UNIX_TIMESTAMP(`yourfield`) BETWEEN UNIX_TIMESTAMP() AND UNIX_TIMESTAMP()+60*60*24*14

 

It does beg the question of why you are storing a date in the datetime format, but in a text field - use a datetime field!

[pocobueno1388]

If that one doesn't work, try this.

 

SELECT * FROM table WHERE `date` BETWEEN NOW() AND NOW() + INTERVAL 14 DAY

 

I agree, what's up with storing it in a TEXT field?

[subnet_rx]

It's not my script, just something open-source, I'm just trying to modify it to get it to work like I want it to. 

[kenrbnsn]

Since you're modifying it, change the type of the field to be datetime.

 

Ken

[Barand]

I agree, what's up with storing it in a TEXT field?

 

DATETIME column = 8 bytes

varchar              = 20 bytes

text                  =  21 bytes