[SOLVED] HELP Needed for Date Between in MYSQL Query

[everurssantosh]

Hi ,

 

I am writing a query in mysql which will find appropriate fields in a table between two dates given.

 

Below is the query I am using

 

select row_id from prop_price_period where pid='1' and firstday  <='13/09/2008' and lastday >='13/09/2008'.

 

This query was working fine until yesterday since I was using the MM/DD/YYYY (USA) date format.

Today we required our application to support both the date formats (USA & UK).

 

So I changed the date format to DD/MM/YYYY.

Now this query is returning wrong result set.

 

Kindly help me to solve this problem

 

Thanks

Santosh

[PFMaBiSmAd]

Your first step to solving this would be to use a mysql DATE data type.

 

You cannot sort or compare either - MM/DD/YYYY or DD/MM/YYYY, because the fields that make them up are not arranged most significant (yyyy) to least significant (dd).

[everurssantosh]

Hi

Thanks for the reply...

 

Can we use Date_format or STR_TO_DATE function in the where clause of the select query to help mysql read the date and understand the format ...

 

Please guide me if this is possible

 

Thanks

Santosh

[PFMaBiSmAd]

You can, but that will result in queries that take at least twice as long to execute because every value in the column must be passed through the function to get the value into a format that can be compared to know if the row matches the WHERE clause.

 

The mysql DATE, TIME, and DATETIME columns exist for a reason, to hold date, time, and datetime values in the most efficient way (least storage - a DATE takes 3 bytes, your existing strings take 11 bytes) and to allow the quickest queries.

 

You would normally use the STR_TO_DATE() function when inserting or updating information to take your MM/DD/YYYY or DD/MM/YYYY format and convert it into a DATE data type and use the DATE_FORMAT() function when selecting information to output a DATE data type any way you want.

[Smystery]

Since my brother can't help me about MYSQL, this topic is very usefull.

Thanks for this topic and thanks PFMaBiSmAd