SQL Between dates issue

[kamal213]

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 

 

[Barand]

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 February 16, 2015 by Barand

[kamal213]

Wow!

 

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!