Comparing dates with between

[paulafernandes]

Hi! I'm back again...

My new problem is this:
I have a form with two combobox's where the user choses an interval of ages. On the validation page I have:

dataactual = date()
diames = left (dataactual, 6)

difdata1 = year(date) - request("idade1")
difdata2 = year(date) - request("idade2")

data11 = diames &difdata1
data22 = diames &difdata2

data1 = formatdatetime (data11, 2)
data2 = formatdatetime (data22, 2)

strsql = "select * from tab_filhos where dn_filhos between '" &data2& "' and '" &data1& "'"
objRecordset.Open strSQL, objConnection, adOpenKeyset

I keep getting this error message:
Microsoft OLE DB Provider for SQL Server error '80040e21'

Errors occurred

The field dn_filhos is a datetime type on my SQLServer 7.0 database.

What's that error? I don't know what I'm doing wrong...

Thank's a lot
Paula

[Roy Sinclair]

The error code you received is defined as:


DB_E_DATAFIELD_OVERFLOW 80040E21 OLE DB Specific:
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.


This indicates that perhaps the date fields you are building aren't coming out right. Have you program sipt out the SQL statement (response.write strsql) before you call the DB so you can examine it and make sure it's proper. You can also then cut and paste it into Query Analyzer where you can fiddle with it until it works and then fix the code to match what you did in Query Analyzer.

[whammy]

P.S. I have some not too bad code here (IMHO) for selecting date ranges if you wanna take a peek at it:

http://www.solidscripts.com/displayscript.asp?sid=18

The Sub "WriteNumericDropdown()" can be used for any select where you're using a range of numbers, by defining the step parameter, you can have it go backwards starting from whatever (for instance 21), with a step of -3 if you want, so it can go backwards or forwards depending upon what you pass to the args.

But it's pretty useful for just writing day and year select dropdowns, as in the example.

Obviously you can extend this idea to simplify writing similar code over and over for lots of different things... ;)

Also, I totally agree with Roy Sinclair - SQL Query Analyzer is your best friend, if you can use it.

I like to have SQL do most of the work for me, so SQL Query Analyzer is usually the first place I go to build what would be a complicated MULTIPLE JOIN or a combination of LEFT OUTER JOIN and INNER JOIN statements between related tables, for instance.

That way, you know you're getting the data you want to begin with, and that your SQL statement works. And once you do a couple, what seemed hard is really child's play, assuming your relational database structure makes some kind of sense.

Once you know it works in the Query Analyzer, all you have to do is plug in the appropriate variables. :)

[paulafernandes]

HI!
I found the problem... I had ' around the dates, so it gave me that error message. Why does the machine has to be always right??? :)

Anyway, thank's to all the hints. Whammy, I didn't knew that site you talk about, I browsed a little, it has pretty neat thing's...

I don't usually use the SQL Query Analyzer, I use instead the views of the SQL Server Interprise Manager. I don't actually use the views, but use it to built some sql string or test it. I don't know if it's better but...

Well, I'm getting to long, so... Thank's!

Paula