Hi,

I am using DB2 database and VB6 on Win XP. I need to select records from Database for some particular month. I am trying to extract YEAR from DATE field (named as INV_DATE) in database table INVOICE using the following code.


strqueryDate = "SELECT EXTRACT (YEAR from INV_DATE) from INVOICE where EXTRACT(YEAR from INV_DATE) = " + CStr(intYear) + ""

Set localrecordsetDate = AGFDB.CONAGF.Execute(strqueryDate)
totalRecCountDate = localrecordsetDate.RecordCount

this query runs well on DB2 database command window but while executing in code it gives me following error:

Run-time error '-2147217900(80040e14)':
[Microsoft][ODBC dBase Driver] Syntax error (missing operator) in query expression 'EXTRACT(YEAR from INV_DATE)'.


I am not able to understand what is wrong. Please help

Anurag

Hello Anu,

Though I am no veteran in this subject, the following occured to my mind when I was parsing your query. But remember I use MS SQL Server 2000 (and you can estimate my knowledge in my thread that is just below yours

1) Extract function is not appropriate in that situation (atleast not in SQL Server). The function required is DATEPART( ). So, your query transforms to:

[code]
strqueryDate = "SELECT DATEPART(year,INV_DATE) from INVOICE where DATEPART(year,INV_DATE) = " + CStr(intYear) + ""
[code]

2) I guess the comparision is a text comparision and hence your CStr function's output should be in single quotes. Hence your query re-transforms to:

[code]
strqueryDate = "SELECT DATEPART(year,INV_DATE) from INVOICE where DATEPART(year,INV_DATE) = '" + CStr(intYear) + "'"
[code]

Thats all as far as I can see. But if it is wrong, dont crush your teeth in my name....look for a better answer from some veteran.

Cheers
-Harsha.