Is it possible to do DATE_SUB( ".$date." , INTERVAL 100 DAY ) if the type of the column where the date is stored is varchar(255) or I need to convert that column to "DATE" type?
views:
34answers:
2
+1
A:
Yes, it's possible if you store your data in yyyy-mm-dd format.
Otherwise you ought to change it to this one.
Col. Shrapnel
2010-06-14 11:49:59
The date is stored in d-m-y format. If I try to query mysql using query 'SELECT * from videos WHERE user_id=$id AND date_uploaded >= DATE_SUB( ".$date." , INTERVAL 100 DAY )' I can't get any result.To convert date I'm using PHP command '$date = date('d-m-y')'What can be the problem with this query?
Sergio
2010-06-14 11:56:16
@Sergio the problem is OBVIOUS: you need to convert date stored in the database. You need to convert that column to "DATE" type, yes. And you will do yourself a HUGE favor by doing that. (Note that first you have to convert this columns's value, or you'll lose the data. )
Col. Shrapnel
2010-06-14 12:48:30
A:
You can convert your date stored as varchar using the STR_TO_DATE() function in mysql. e.g.
DATE_SUB(STR_TO_DATE(date_column_name,'%d-%m-%y'),INTERVAL 100 DAY) , just make sure the date formatting string matches the format of whatever is in you date column.
nos
2010-06-14 12:01:21
+1: Better to explicitly convert, if not actually correct the data type for the column in question.
OMG Ponies
2010-06-14 14:50:47