Solved

Date Format

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

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.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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