Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Convert string to date in mysql query

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
Kasper Katzmann
Asked:
Kasper Katzmann
  • 7
  • 2
  • 2
  • +2
1 Solution
 
Ray PaseurCommented:
You want to modify the date columns.  Define them as DATETIME fields, and use ISO-8601 format for all internal date representations.
0
 
Mark BradyPrincipal Data EngineerCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
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
 
dsmileCommented:
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
 
raulggonzalezCommented:
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
 
Mark BradyPrincipal Data EngineerCommented:
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
 
raulggonzalezCommented:
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
 
Ray PaseurCommented:
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
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
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
 
Ray PaseurCommented:
Please post the COMPLETE code, thank you.
0
 
Ray PaseurCommented:
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
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
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
 
Ray PaseurCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now