Jump to content


Photo

Returning rows from mssql database between two dates *SOLVED*


  • Please log in to reply
2 replies to this topic

#1 Chips

Chips
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 03 July 2006 - 10:23 AM

Trying to create a news archive where a user can view news items from a month. In other words, select the month/year and all the news items from the 1st of that month until the 31st of that month will be returned.

$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 :(

#2 Chips

Chips
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 03 July 2006 - 01:58 PM

It works when the code is this:
$query = mssql_query("SELECT * from " . _CONTENTDB_ . "  where section = (SELECT section from " . _SECTIONSDB_ . " where sectionName = 'news') AND date BETWEEN '2006-06-01' AND '2006-06-31' ORDER BY date DESC;");

But if I try to make a $date1 and $date2 to replace the two dates inputted, it no longer works. Any suggestions anyone at all?

Tried:
where sectionName = 'news') AND convert(varchar, date) between '$date1' AND '$date2'

where sectionName = 'news') AND date between convert(datetime, $date1) AND convert(datetime, $date2)

The $date1 is made up of a concatenated variables -
$date1 = $year . $month . $day;
But also:
$date1 = $year . "/" . $month . "/" . $day;
$date1 = $year ."-".$month."-".$day;
All have been tried, fiddled and experimented.
Can't seem to work out how this should be done, any help grateful.


#3 Chips

Chips
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 03 July 2006 - 02:22 PM

Okay, so turns out there are only 30 days in June, and getting the number of days wrong in a month for a query is a very bad idea :(




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users