Jump to content

help with sql between


vj5
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...