Solved

Date Format

Posted on 2013-06-24
9
371 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

806 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