Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert string to date in mysql query

Posted on 2010-11-12
14
Medium Priority
?
599 Views
Last Modified: 2012-05-11
I have two fields (startdate and enddate) in my table that contains a date in the format dd/mm/yyyy. The date is stored as text.

Now I want to make a query that only selects the rows where the two dates are between today and the date in the second. I have done like this, but it doesn't work:

SELECT * FROM tbl_konk WHERE `startdate` >= CURDATE() AND `enddate` < CURDATE()

The connection is up and running and works until I use the WHERE clause.

I guess I have to use some kind of conversion, but I cant figure out how.

Anyone that can help?

Regards
Kasper
0
Comment
Question by:Kasper Katzmann
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34125001
You want to modify the date columns.  Define them as DATETIME fields, and use ISO-8601 format for all internal date representations.
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34125010
Have you tried "BETWEEN `startdate` AND `enddate`"

I'm just going off the top of my head but the logic suggests that you select * from your table and return all records where date is between your startdate and enddate. Not sure of the exact syntax but give that a try.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34125025
And once you've got the fields defined correctly you definitely want to use BETWEEN - it is about twice as fast as making two inequality comparisons.
0
 
LVL 13

Expert Comment

by:dsmile
ID: 34126292
I you CANNOT change your table design, then you can use string to date conversion, then do the comparing

SELECT * FROM tbl_konk WHERE CURDATE() BETWEEN STR_TO_DATE(`startdate`, '%d/%m/%Y')  AND STR_TO_DATE(`enddate`, '%d/%m/%Y')

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34126506
Hello,

just a small detail, you say `startdate` and `enddate` , but those aren't single quotes, they look like stress marks instead.

Use single quotes like 'startdate' and 'enddate' together with any example from above.

good luck
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34126920
They are not stress marks as such, they are called back ticks and are used when you need to enclose a string including spaces so we use back ticks ` ` that is the standard MYSQL method.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34126947
Hi @elvin66,

Thanks for pointing this, I'm from MS SQL and we use [] for these cases.

For anyone with the same problem with quotes and backticks

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Cheers.

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34127527
Where I wrote, "You want to modify the date columns" I might have been more helpful if I had offered a little more explanation.

Back up your table before you do anything to it.  Use ALTER TABLE to add the correctly defined DATETIME columns. Then run a series of queries along the lines of ID:34126292 to move the existing pseudo-dates into the new columns.

Thanks for asking this question -- it has got me thinking more about PHP and MySQL DATETIME issues, and I will update my article with better examples and more explanation soon,

best to all, ~Ray
0
 

Author Comment

by:Kasper Katzmann
ID: 34127542
Ok, the fields are now set to date fields and they contains dates in the format 2010-11-13.

I have changed the SQL string to this:
$query="select * FROM `content_type_konkurrence` WHERE " . date("Y-m-d") . " BETWEEN `startdate` AND `enddate`";

One of the fields in the table has these values: startdate=2010-11-10 and enddate=2010-12-24

But there is no output at all. No errors, just an empty page.

0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 34127571
Please post the COMPLETE code, thank you.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34127575
It is likely that you want something more like this, but the more important issue is getting the error messages.
$my_date = date('c');
$query   = "SELECT * FROM content_type_konkurrence WHERE '$my_date' BETWEEN startdate AND enddate";

Open in new window

0
 

Author Closing Comment

by:Kasper Katzmann
ID: 34128253
Thank you very much. Your code did the trick.

What does the 'c' do in the date function? Is it for "Current"?

I have just recently (this week) decided to change from ASP/MS SQL to php/mysql and my ASP and SQL are quite rusty, so lots of questions are on the way (if I cant google it).
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34128872
The "c" in the date() function arguments produces the ISO-8601 DATETIME string.  All of the PHP functions are documented on the PHP.net web site.  Example of date() here:
http://us2.php.net/manual/en/function.date.php

Anyway, thanks for the points and when you need help figuring out errors, please post the code that is generating the error.  It makes us able to help faster.  Best regards, ~Ray
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

926 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