Jump to content

Archived

This topic is now archived and is closed to further replies.

Chips

Returning rows from mssql database between two dates *SOLVED*

Recommended Posts

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.

[code]$query = mssql_query("SELECT * from " . _CONTENTBD_ . " where section = '2' and date BETWEEN  $date1 AND $date2[/code]

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:
[code]$date1 = $year ."/". $month ."/01";
    $date2 = $year ."/". $month ."/31";[/code]
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 :(

Share this post


Link to post
Share on other sites
It works when the code is this:
[code]$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;");[/code]

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:[code]
where sectionName = 'news') AND convert(varchar, date) between '$date1' AND '$date2'[/code]

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

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

Share this post


Link to post
Share on other sites
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 :(

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.