Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    30

    Red face Unanswered: convert statement

    hi,

    i got an error while running a sql query in VB. The query is:

    rs1.Open "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > '" & _
    Format(DateAdd("d", 30, Date), "dd/mm/yy") & "'", Conn

    when compile i receive this error message:

    Undefined function "convert" in expression.

    I'm using SQL 2000 and user_def_fld_3 is a char(8) by defination.

    Thanks.
    John

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: convert statement

    Try using:

    rs1.Open "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > " & _
    " Format(DateAdd(d, 30, Date), 'dd/mm/yy') ", Conn



    Originally posted by air_salak
    hi,

    i got an error while running a sql query in VB. The query is:

    rs1.Open "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > '" & _
    Format(DateAdd("d", 30, Date), "dd/mm/yy") & "'", Conn

    when compile i receive this error message:

    Undefined function "convert" in expression.

    I'm using SQL 2000 and user_def_fld_3 is a char(8) by defination.

    Thanks.
    John
    Steve

  3. #3
    Join Date
    Jul 2002
    Posts
    30

    Re: convert statement

    Originally posted by dbadelphes
    Try using:

    rs1.Open "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > " & _
    " Format(DateAdd(d, 30, Date), 'dd/mm/yy') ", Conn

    Taking away the double quote will cause the 'd' to be not defined. Thus the error "variable not defined" appear.

    As you can see, I'm using VB to run this SQL statement. Any other idea?

    Thanks,

    John

  4. #4
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: convert statement

    That's not supposed to cause an error because the syntax for dateadd is
    DATEADD(datepart, number, date)
    where the abbreviation for datepart can be day,dd or d (see Books Online). To make sure, just type DATEADD(d, 30, getdate()) in Query Analyser and see that it passes, so I think that the problem is somewhere else.
    Format function doens't exists in SQL2000. Try using CONVERT or CAST, like, for instance:

    CONVERT(VARCHAR, DATEADD(d, 30, your_date), 103)

    Bottom line, the SQL statement to pass to SQL Server (BTW you can store it in a VB string var) would be:

    Dim strSQL as String
    strSQL = "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > CONVERT(DATETIME, DATEADD(d, 30, " & _
    your_vb_date_var & "), 103)"

    rs1.Open strSQL, Conn



    Originally posted by air_salak
    Taking away the double quote will cause the 'd' to be not defined. Thus the error "variable not defined" appear.

    As you can see, I'm using VB to run this SQL statement. Any other idea?

    Thanks,

    John
    Steve

  5. #5
    Join Date
    Jul 2002
    Posts
    30
    ok, i think my problem lies back to the initial problem. That is" undefined function "convert" in expresion.

    I can run the sql statement in query analyzer but using VB this problem hit out. I tested a lot of sql functions in vb and they can work nicely but this convert function is otherwise. Is there any other alternative for me?

    User_def_fld_3 is char type and have the date data. i need to select records that is almost expired(30 days) and already expired.

    Thanks for yur help.

    John

Posting Permissions

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