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.

SELECT
tblcards.TransferStatus
FROM
tblcards
WHERE
`tblcards`.`TransferStatus` >= ' 2013-06-19'

Open in new window

BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
 
nemws1Connect With a Mentor Database AdministratorCommented:
You're missing the slashes in the date, though.  Give this a try! ;-)


SELECT tblcards.TransferStatus
FROM tblcards
WHERE STR_TO_DATE(`tblcards`.`TransferStatus`, '%Y/%m/%d %H:%i:%S') >= ' 2013-06-19'

Open in new window


Always a good idea to check your STR_TO_DATE() call with a SELECT:

SELECT STR_TO_DATE(`tblcards`.`TransferStatus`, '%Y/%m/%d %H:%i:%S')
FROM tblcards
LIMIT 5

Open in new window

0
 
Jagadishwor DulalBraces MediaCommented:
What kinds of data is that and what exactly you want to do?
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
datetransfer is a varchar not a datetime as it should have been, I now have to filter from a date forward
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nemws1Database AdministratorCommented:
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:

SELECT tblcards.TransferStatus
FROM tblcards
WHERE STR_TO_DATE(`tblcards`.`TransferStatus`, '%d %M %Y %H:%i:%S') >= ' 2013-06-19'

Open in new window

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
That is returning no records
0
 
nemws1Database AdministratorCommented:
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.
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
date format returned is 2013/06/23 17:58:08

I edited your code

SELECT tblcards.TransferStatus
FROM tblcards
WHERE STR_TO_DATE(`tblcards`.`TransferStatus`, '%Y %m %d %H:%i:%S') >= ' 2013-06-19'

Open in new window


do we need the Time format?
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
Got it working 2013/06/19 09:08:32

STR_TO_DATE(`tblcards`.`TransferStatus`, '%Y/%m/%d %H:%i:%S'

Open in new window

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
Excellent conclusion, thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.