Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: SQL Query for DATE Field


    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


  2. #2
    Join Date
    Jan 2004

    Arrow Try this....

    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:

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

    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:

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

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts