Str To Date Not Working

[Alicia]

Hi guys,

 

can somebody give me an idea why this query doesn't work? thanks

 

SELECT SUM(credit) AS usage,  STR_TO_DATE(`created`, '%m/%d/%y'),STR_TO_DATE(`expiry`, '%m/%d/%y') FROM `suax` WHERE  STR_TO_DATE(`created`, '%m/%d/%y') >= '11/5/2012' AND STR_TO_DATE(`expiry`, '%m/%d/%y') = '11/15/2012' LIMIT 0 , 30

 

my created and expiry format is a string like this 1/15/2012 04:05:22

 

 

Please advise.thanks

[Pikachu2000]

In what way does it not work, pray tell?

[Alicia]

i just want to filter the records by referrring to the date i have in the column but currently it is in varchar so i couldn't filter by date range

[Pikachu2000]

Again, in what way is it not working?

[Alicia]

i am not sure... there is a syntax error when i run the query

[Jessica]

One problem I see is that 1/15/2012 04:05:22 is NOT '%m/%d/%y'. 1/15/2012 would be %m/%d/%y. I've NEVER used that function so I don't know if that it is.

 

However, you got a syntax error, and you didn't post it...why exactly? 

[Barand]

So many errors!

 

You are not correctly defining the current format to be converted (see Jessica's reply)

You are converting to mysql date format yet comparing against your string format (use >= 'yyyy-mm-dd' etc)

You logic is screwed - you look for those greater than one date AND equal to another (only ever returning the latter)

You need column aliases for your converted dates

 

EDIT:

The time element will be ignored

mysql> select STR_TO_DATE('22/01/2012 09:30:00','%d/%m/%Y') as dob;
+------------+
| dob	 |
+------------+
| 2012-01-22 |
+------------+

Edited November 15, 2012 by Barand