Can anyone help?

I have a form with 4 fields (dropdown lists). frommonth, fromyear, tomonth, toyear. A user can select from the drop down list a month and a year.

Im trying to pull out records where the date (cc_date) is between a specified date. For example January 2006 to December 2006. So frommonth = 01 fromyear = 2006 tomonth= 12 toyear=2006

Here are my variables:

Name: Default Value Runtimevalue
fromdate 01/01/2006 Request.QueryString("frommonth" &"/01/"&Request.QueryString("fromyear"
todate 12/31/2006 Request.QueryString("tomonth"&"/31/"&Request.QueryString("toyear"

Here is my SQL

SELECT *
FROM dbo.Contacts, dbo.Customer_Contact, dbo.Customer_Contact_Type
WHERE InstitutionsOrganisations_ID = MMColParam AND C_ID= Contact_Id AND cc_date >= fromdate AND cc_date <= todate AND dbo.customer_contact.CCT_ID = dbo.customer_contact_type.CCT_ID
ORDER BY cc_date asc

It doesn't seem to work. I've tried allsorts and it's really frustrating me. I've tried casting, flooring, converting but to no avail.

From what I can see the database is storing the date as mm/dd/yyyy without any time.

Can anyone point me in the right direction?

I have found dates are really "messy" - depending on what type of field you store you get different things back, some get saved as YYYYMMDDHHMMSS and others are unix seconds, I ended up writing some functions to reformat the input of textboxes from dd mm yyyy into the right format.

It would probably have been better if I had started off with the right date formats in my tables, but by the time someone was asking for textboxes to enter dates in separate parts it was too late.

Sorry that does not give you the answer - just something to consider

Thanks for this Alan

I totally agree dates are so messy. It's really strange though, if I go onto the server and into the MS SQL the dates are stored in a date/time field (8) and the dates are in the correct UK format DD/MM/YYYY. When I access the database through dreamweaver the dates come back as SAT APR 4 00:00:00 UTC +100 2006! And when I pull them into a web page they come out as MM/DD/YYYY. So Im totally confused as to what format they are actually in!!

the manual page is here - you have probably seen it already

dev.mysql.com/doc/refman/5.0/en/datetime.html

the kind of thing I did was . . .

function reformatDateTime($formatString, $date)
{
# takes a date in the form YYYY-MM-DD HH:MM:SS
# first it is converted to a unix timestamp (seconds on the unix era)
# then formatterd using the php date function
# this is needed because the date formatting was not specified at the outset
# in which case mysql date formatting could have been used
# formatString is the php date function string - see php function docs
#
# Alan C May 2006
$date=substr($date,0,10);
$year = substr($date,0,4);
$month= substr($date,5,2);
$day = substr($date,8,2);
return trim(date($formatString, mktime(0, 0, 0, $month, $day, $year)));
}

which is horrible, there must be a better way but when you need to get something working it's often not done the best way.