$query = mssql_query("SELECT * from " . _CONTENTBD_ . " where section = '2' and date BETWEEN $date1 AND $date2
The DATE field is in smalldatetime format - so yielding yyyy-mm-dd hh:mm:ss. Obviously the ss is always 00 being a smalldatetime format.
Currently the user has two drop down html boxes with the months (1-12) and years (current year onwards only, so 2006 available at the moment).
Code for calculating my dates is:
$date1 = $year ."/". $month ."/01"; $date2 = $year ."/". $month ."/31";Obviously yields yyyy/mm/dd as the format. I have tried this, or with dashes as well. I have also done that with adding 00:00:00 to the above dates to give a format of yyyy/mm/dd hh:mm:ss as well (matching the datetime field).
My query, obviously, returns nothing. There are 3 news items with dates between my values, and none are brought back. I have tried doing a convert, but found no real documentation that helped too much (Trying to convert the date field to a varchar and then see if it's between the two dates, which I assume are string formats at the present time) but this didn't appear to work (problem being I don't know if i executed this correctly).
I've spent quite some time searching the web and forums too - found similar problems, but using mysql instead of mssql - and their solutions haven't worked for me either!
Any suggestions, hints, tips or pointers would be highly appreciated. Having spent the best part or 2 hours fiddling, it's obviously hit the time where any more time will be wasted without help