Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Convert string to date in mysql query

Posted on 2010-11-12
14
589 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 109

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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 34125012
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 109

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 109

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 109

Accepted Solution

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

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 109

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

808 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