Jump to content

Returning rows from mssql database between two dates *SOLVED*


Chips

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.