Solved

Convert string to date in mysql query

Posted on 2010-11-12
14
591 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
[X]
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
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 110

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 110

Expert Comment

by:Ray Paseur
ID: 34125012
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 110

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 110

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 110

Accepted Solution

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

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 110

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
The viewer will learn how to dynamically set the form action using jQuery.

738 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