Jump to content

SQL Between dates issue

Recommended Posts

Hi there,


I'm basically trying to do the following in mysql:


SELECT sale_date FROM customer-sale WHERE sale_date BETWEEN '05/02/2015' AND '10/02/2015'


However I keep getting 0 results, I found out that the issue is with my date format as it should be in (YYYY-mm-dd) format. Now I know, wish I know so about a year ago as I already have over 1500 records on my customer table so to late to change.


So I need help with the following:

1.) Is there a search query that can convert dd/mm/yyyy into YYYY-mm-dd so I can run the above query

2.) Is there a way to convert the dates using PHP


Need help


Thanks a alot 


Share this post

Link to post
Share on other sites

You can use the MySQL function STR_TO_DATE() to convert your dates to the correct format

SELECT sale_date 
FROM `customer-sale` 
WHERE STR_TO_DATE(sale_date,'%d/%m/%Y') BETWEEN '2015-02-05' AND '2015-02-10'

You can use that same function in an UPDATE query to convert your dates to the correct format. Add new DATE type column to store the correct format then

UPDATE `customer-sale`
SET newdate = STR_TO_DATE(sale_date, '%d/%m/%Y')
Edited by Barand
  • Like 1

Share this post

Link to post
Share on other sites



Barand you are a PHP god in human form!


Thanks a lot that's exactly what I was looking for and more.


Love the fact I can even update the previous records into a new column with the correct format.


Thanks a bunch men, Amazing!


Have a nice day!

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.