Invalid operator for data type boolean - datetime PLEASE HELP

[jennypretty]

HELLO FRIENDS,
I am trying to select only 4 digits of year but it keeps generating errors. This is the date of my table "04/13/2005", and I am trying to select the year ONLY. I used DATEPART to convert in sql server.
Below is the block that generates errors.
Can you please help me?
Thanks,
Jenny.

rptStr = "select Ref_Date_Id, ref_begin_date, Ref_Begin_Date & ' - ' & Ref_End_Date from Ref_Date"
rptStr = rptStr & " order by Ref_Date_Id Asc"
Set rs = Server.CreateObject ("ADODB.Recordset")

rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
if (fyValue = 0) or (DatePart("YYYY",rs(1)) = Cint(fyValue)) then
Response.Write("<OPTION value=" & rs(0) & ">")
Response.Write(rs(2))
Response.Write("</OPTION>")

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals boolean AND, type equals datetime.

 

[Sheco]

So you still see the error from the original message?

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals boolean AND, type equals datetime


What line does it say the error is on?

 

[bslintx]

jenn is the field an int or date/time?

 

[jennypretty]

This line, Set rs = dbConn.execute(rptStr)

jenbegin_date, jenEnd_Date are datetime.

Thanks,

Jenny.

 

[Sheco]

That line is commented out in the file that you posted on 13 Apr 05 11:38 .

But assuming the file has changed so that it is not commented out, the problem would be 1 of 2 things:
A) A problem with the ADO Connection object or
B) A problem with your SQL statement.

To figure out which it is, add the following immediately before that line:

[red]
Response.Write "rptStr = " & rptStr & "<BR><BR>"
Response.Write "dbConn.State = " & dbConn
Response.End
[/red]
Set rs = dbConn.execute(rptStr)
...

The Response.End statement will halt execution of the script immediately before the error. Add the 3 lines in red above and let us know what you see.

 

[JSpicolli]

One issue is this line:

rptStr = "select Ref_Date_Id, ref_begin_date, Ref_Begin_Date & ' - ' & Ref_End_Date from Ref_Date"

Not sure what you are trying to do there, but it isn't valid SQL.

Are you trying to concatenate Ref_Begin_Date and Ref_End_Date ?

 

[JSpicolli]

I think you meant:

rptStr = "select Ref_Date_Id, ref_begin_date, (Ref_Begin_Date - Ref_End_Date) as MeanDate from Ref_Date"

OR perhaps....

rptStr = "SELECT Ref_Date_ID, Ref_begin_Date, MeanDate = ref_begin_date - (DateDiff(day, Ref_begin_Date, ref_begin_date)) from Ref_Date"

Depends on exactly what you want to do.

 

[JSpicolli]

Whops!

Excuse my type-o.

Should read:

rptStr = "SELECT Ref_Date_ID, Ref_begin_Date, MeanDate = ref_begin_date - (DateDiff(day, Ref_begin_Date, ref_end_date)) from Ref_Date"

Again, just guessing becuz I am not sure what you are trying to accomplish. But your issue IS your SQL staement.

 

[jennypretty]

Ref_Begin_Date & ' - ' & Ref_End_Date
I am trying to display the begin date and end date like this: 02/23/2001 - 04/14/2005
Thanks,
Jenny.

 

[Sheco]

Try maybe using BETWEEN for an inclusive range or, for an exclusive range use (x > y ) and (y < z)

 

[JSpicolli]

You need to Concatenate them AND cast them as a string then.

rptStr = "select Ref_Date_Id, Cast(Ref_Begin_Date as varchar(50)) + ' - ' + Cast(Ref_End_Date as varchar(50)) as MyDate"

HTH

 

[Sheco]

Ah so this is for display purposes only, not part of the search criteria?

 

[JSpicolli]

Another option is to Do a simple select:

rptStr = "SELECT Ref_Date_Id, Ref_Begin_Date, Ref_End_Date FROM Ref_Date"

'Open your RS - Blah blah blah

Dim sMyDates
sMyDate = oRS("Ref_Begin_Date") & " - " & oRS("Ref_End_Date")

Response.Write(sMyDates)

 

[JSpicolli]

The addition of the Debug lines will help to isolate the error. You'll remove them when the issue is resolved

that's why I use VS.NET for classic ASP too.

No need for medival response.write debug's, just set breakpoints and step thru the code.

 

[jennypretty]

JSpicolli, woh woh woh. It worked. I used the CAST function and it worked.
Big thanksssssssss...
Jenny.

 

[Sheco]

Good catch JSPicolli. I looked back over it and have no idea how I got the idea in my head that this was part of the WHERE criteria, it clearly wasn't.

 

[JSpicolli]

thnx sheco, glad it worked out for ya Jenny.

 

[bslintx]

jenny...please don't forget those that you thought helped you get the fix action...a lil recognition goes a long way! ;-)

nice solve JSP! and glad you got a fix jenny