help with sql between

[vj5]

I am trying to get a range of date in the search and I my code is :

if (strlen($cdos)!=0) {$QRY1 = $QRY1." AND `dos` between ('2008/01/01', 'yyyy/mm/dd')	AND ('2008/12/31', 'yyyy/mm/dd')";}

cdos is the variable for the date. dos is the field in the database.Can someone give the correct syntax to use between statement to help search date range.

[justsomeguy]

between CAST('2008/01/01' AS date) AND CAST('2008/12/31' AS date)

[vj5]

between CAST('2008/01/01' AS date) AND CAST('2008/12/31' AS date)

What is CAST? Another thing, I have 20080220 format in the database, will it search 2008/12/31?

[justsomeguy]

CAST converts between data types. In this case you are converting a string to a date, which will match what is stored in the database. If the column you are comparing with is something other then date, like datetime, then change the cast to reflect that.

[vj5]

CAST converts between data types. In this case you are converting a string to a date, which will match what is stored in the database. If the column you are comparing with is something other then date, like datetime, then change the cast to reflect that.

Thankyou. I have varchar (15) for the date column. What can I use to reflect that?

[justsomeguy]

That's not a good choice. You're not storing a date, you're storing a string. If you try to compare that using BETWEEN you are going to get an alphabetical comparison, not a chronological comparison. Either change your columns to be date, or what I do personally is set my date or time columns to be int and then I store integer timestamps, where you can just use operations like less then and greater then instead of BETWEEN. You can get the current integer timestamp in PHP using the time() function.

[vj5]

That's not a good choice. You're not storing a date, you're storing a string. If you try to compare that using BETWEEN you are going to get an alphabetical comparison, not a chronological comparison. Either change your columns to be date, or what I do personally is set my date or time columns to be int and then I store integer timestamps, where you can just use operations like less then and greater then instead of BETWEEN. You can get the current integer timestamp in PHP using the time() function.

Thanks. I have changed into Datetime field. In the database, it displays as 2008-02-04 00:00:00. How will I display in php with that format? I am having problem with that format.

[justsomeguy]

http://us2.php.net/manual/en/function.strtotime.php