Solved

Date Format

Posted on 2013-06-24
9
369 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
 

Author Comment

by:Brogrim
ID: 39271914
That is returning no records
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.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now