Solved

Date Format

Posted on 2013-06-24
9
373 Views
Last Modified: 2013-06-24
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

0
Comment
Question by:Brogrim
  • 5
  • 3
9 Comments
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39271496
What kinds of data is that and what exactly you want to do?
0
 

Author Comment

by:Brogrim
ID: 39271589
datetransfer is a varchar not a datetime as it should have been, I now have to filter from a date forward
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39271838
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:Brogrim
ID: 39271914
That is returning no records
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39271922
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
 

Author Comment

by:Brogrim
ID: 39271947
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
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 39271965
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
 

Author Comment

by:Brogrim
ID: 39271976
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
 

Author Closing Comment

by:Brogrim
ID: 39271983
Excellent conclusion, thank you very much
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question