Comparing Date

[Alicia]

Guys, can someone give me a hint on this.

 

Currently i have 2 columns in db to store the date but the structure is in varchar

 

How can I compare the date with these columns because I did try to use str to date function but now working.

 

My script as below.

my column name is join and grad and data store in these varchar columns format is like this : 14/03/2001 11:30:00

 

$now = date("Y-m-d");

$beu_main = mysql_query("SELECT * FROM `students` WHERE STR_TO_DATE( join, '%d/%m/%Y' )<='$now' AND STR_TO_DATE( grad, '%d/%m/%Y' ) > '$now'");

 

 

 

anything wrong with my query or any quick fix? thankyou.

[Barand]

Apart from your using varchar for dates (and a useless format) instead of datetime (YYYY-MM-DD HH:ii:ss), and instead of '$now' you could use CURDATE().

 

I cannot see any obvious errors. Define "not working" and check your data.

[teng84]

maybe type casting would help you

[Barand]

shouldn't need typecasting

mysql> SELECT STR_TO_DATE('08/10/2012 07:30:00','%d/%m/%Y') = CURDATE() as test;

+------+
| test |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

[Alicia]

thanks guys

 

but how can i sort this column by showing the latest date first since this is a varchar column?

 

i tried

$re= mysql_query("SELECT * FROM `students` ORDER BY STR_TO_DATE( join, '%d/%m/%Y' ) DESC");

 

but it is not working.. can someone pls assist..thanks

[Barand]

Sorry for not spotting this earlier.

 

Your date column name "join" is a reserved word in SQL. You should either rename it (recommended) to something like join_date or enclose the name in backticks in your queries eg

STR_TO_DATE(`join`, '%d/%m/%Y')

 

If you check the value of mysql_error() when you get problems you should get there a little faster.

[Alicia]

i have changed that but it is still not showing the latest date on top.. any idea? thanks

[Barand]

I set up a test table and this looks OK

mysql> SELECT * FROM students;
+-----+----------+---------------------+
| pid | username | join			    |
+-----+----------+---------------------+
|   1 | MMDE1    | 01/11/2012 09:30:55 |
|   2 | MMDE2    | 05/10/2012 08:55:00 |
|   3 | MMDE3    | 08/10/2012 12:25:00 |
|   4 | MMDE4    | 06/10/2012 15:30:00 |
+-----+----------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM students ORDER BY STR_TO_DATE(`join`, '%d/%m/%Y') DESC;
+-----+----------+---------------------+
| pid | username | join			    |
+-----+----------+---------------------+
|   1 | MMDE1    | 01/11/2012 09:30:55 |
|   3 | MMDE3    | 08/10/2012 12:25:00 |
|   4 | MMDE4    | 06/10/2012 15:30:00 |
|   2 | MMDE2    | 05/10/2012 08:55:00 |
+-----+----------+---------------------+
4 rows in set, 4 warnings (0.00 sec)