subnet_rx Posted November 13, 2007 Share Posted November 13, 2007 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? Link to comment https://forums.phpfreaks.com/topic/77182-need-rows-with-certain-dates/ Share on other sites More sharing options...
GingerRobot Posted November 13, 2007 Share Posted November 13, 2007 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! Link to comment https://forums.phpfreaks.com/topic/77182-need-rows-with-certain-dates/#findComment-390794 Share on other sites More sharing options...
pocobueno1388 Posted November 13, 2007 Share Posted November 13, 2007 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? Link to comment https://forums.phpfreaks.com/topic/77182-need-rows-with-certain-dates/#findComment-390795 Share on other sites More sharing options...
subnet_rx Posted November 13, 2007 Author Share Posted November 13, 2007 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. Link to comment https://forums.phpfreaks.com/topic/77182-need-rows-with-certain-dates/#findComment-390847 Share on other sites More sharing options...
kenrbnsn Posted November 13, 2007 Share Posted November 13, 2007 Since you're modifying it, change the type of the field to be datetime. Ken Link to comment https://forums.phpfreaks.com/topic/77182-need-rows-with-certain-dates/#findComment-390877 Share on other sites More sharing options...
Barand Posted November 13, 2007 Share Posted November 13, 2007 I agree, what's up with storing it in a TEXT field? DATETIME column = 8 bytes varchar = 20 bytes text = 21 bytes Link to comment https://forums.phpfreaks.com/topic/77182-need-rows-with-certain-dates/#findComment-390890 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.