Date Format
I have a column with a varchar(45) that I cant change. I want to query the records on a date.
can i write the query to cast the values as datetime and then query it.
can i write the query to cast the values as datetime and then query it.
SELECT
tblcards.TransferStatus
FROM
tblcards
WHERE
`tblcards`.`TransferStatus` >= ' 2013-06-19'
Jagadishwor Dulal
What kinds of data is that and what exactly you want to do?
ASKER
datetransfer is a varchar not a datetime as it should have been, I now have to filter from a date forward
Yes, but what does the date *look* like? There are many date formats out there. What you'll want to use is STR_TO_DATE:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
If your date looks like this: "24 Jun 2013 10:14:12", you'll use a query like this:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
If your date looks like this: "24 Jun 2013 10:14:12", you'll use a query like this:
SELECT tblcards.TransferStatus
FROM tblcards
WHERE STR_TO_DATE(`tblcards`.`TransferStatus`, '%d %M %Y %H:%i:%S') >= ' 2013-06-19'ASKER
That is returning no records
Yes - we need the correct format for the date, as I said.
What does the date *look* like in your VARCHAR field??? Please post some example rows.
What does the date *look* like in your VARCHAR field??? Please post some example rows.
ASKER
date format returned is 2013/06/23 17:58:08
I edited your code
do we need the Time format?
I edited your code
SELECT tblcards.TransferStatus
FROM tblcards
WHERE STR_TO_DATE(`tblcards`.`TransferStatus`, '%Y %m %d %H:%i:%S') >= ' 2013-06-19'do we need the Time format?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it working 2013/06/19 09:08:32
STR_TO_DATE(`tblcards`.`TransferStatus`, '%Y/%m/%d %H:%i:%S'ASKER
Excellent conclusion, thank you very much