Solved

Convert string to date in mysql query

Posted on 2010-11-12
14
584 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
Please post the COMPLETE code, thank you.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

743 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

18 Experts available now in Live!

Get 1:1 Help Now