February Mucks it all up!

Date problem with ASP/SQL

[bateman_ap]

Wonder if anyone has any ideas on this one, I am writing a SELECT statement that checks to see if a date falls in a particular month. Currently it is using

WHERE fld_date_start BETWEEN '"&startdate&"' AND '"&enddate&"'

where startdate and enddate are manually created from varibles but in the form 12/11/2003 etc.

The startdate is currently always month/01/2003 and the end date is always month/xx/2003 where xx is the last day of the month, looked up with (prob not a good way to do it but sorta works)

if thismonth = 4 OR 6 OR 9 OR 11 then
endday = 30
end if
if thismonth = 2 then
endday = 28
end if
if thismonth = 1 OR 3 OR 5 OR 7 OR 8 OR 10 OR 12 then
endday = 31
end if

THe problem is that it works for every month except February, try to go to that page and it spits back
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

Is driving me mad! Anyone have any idea?

[garann]

I can't offer any help as to why it's throwing that error.. Have you tried writing the values out to the page to check that the enddate variable is actually "2/28/2003"?

I might have an alternative, if you're interested.. Rather than using the if..then statements, you could add one to your month and set the enddate to be the first day of the next month, minus one day. I think that looks like this:
enddate = dateadd("d",-1,(thismonth & "/1/2003"))

I know ASP.NET has a daysInMonth() function - too bad it doesn't seem to exist in classic ASP.

Good luck!
g.

[Dreamquick]

If this is just a simple issue of data conversion when it hits the db engine issues there are two possible solutions;

1) Write the date out containing the literal month, so "February/28/2003" - this is ugly but avoids confusion between your choice of input and the db engine's interpretation of your input.

2) Explicitly convert the date using the CONVERT function (assuming you are using SQL Server) with the correct format identifier for your input date... eg

CONVERT( SMALLDATETIME, '02/28/2003', 101 )

What does "101" at the end do? Well it says that your input is in US format and that the year includes the century (ie format = mm/dd/yyyy).

Aside from that I'd be tempted to use the 1st of next month -1 day approach as it's nice and simple and moves the onus of using the correct logic off you and onto the underlying OS.

- Tony

[killroy]

Actually in MySQL, using:

BETWEEN "01/02/2003" AND "01/03/2003"

will only select february. Why? Because "mm/dd/yyyy" is equivalent to "mm/dd/yyyy 00:00:00" i.e. the VERY BEGINNING OF THE DAY. Which means even a microsecond into the first of the next month will already fall outside the range.

and if it bothers oyu, just write the time explicitly, i.e.:

BETWEEN "01/02/2003 00:00:00" AND "01/03/2003 00:00:00"

So there is no need for fancy ifs or complex conversions.

SN

[txbakers]

in mySql dates are in yyyy-mm-dd format right?

I always convert my mm/dd/yyyy dates into that format before making those kinds of queries.

[Zaphod Beeblebrox]

Why don't you simply test using: WHERE Month(fld_date_start)=2 AND Year(fld_date_start)=2003?

[bateman_ap]

1st of next month -1 day approach, many thanks for that, didn't find that anywhere when I was searching for solutions!