Equal or less then, does not work when passing a date to SQL via vba for Teradata

  • Hi all,


    I'm able to run SQL in Teradata SQL Assistant and getting results.
    When I connect via excel and run the query from there, the rest of the query works, but the equal or less than date not.


    This is the vba code for hte SQL query:

    Code
    thisSql = "SELECT  TOP 1 BALANCE  " & _
    "FROM tbl_CUSTOMER_DB  " & _
    "WHERE ((BRANCH_NO)= " & branch & ") AND ((ACCOUNT_NO)= " & Acc & ") AND ((MVT_DATE) <= " & ToDate & ") Order By MVT_DATE desc "


    It totaly ignores the "<=" as the date in the "ToDate" is the 31/08/2016 and in the outcome of the query dates in end of September are in the query results.
    (dates are entered in Teradata SQL as text: 20160831)


    Any chances this can be fixed?


    Greetings

  • Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata


    Assuming that the BRANCH_NO & ACCOUNT_NO fields are truly numeric and that "dates are entered in Teradata SQL as text" and that the variable ToDate has been populated with a date in the correct format (lots of assumptions), then the following may work:


    Code
    ...AND ((MVT_DATE) <= " & Chr(34) & ToDate & Chr(34) & ") Order By...
  • Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata


    hi Cytop,


    Looks like I got it wrong. When I enter a date directly in Teradata, it needs to be like this: '2016.08.31'
    From the vba sql, it needs to be like this 20160831, but now I wonder if it is numeric or just general format (in the excel format of the cell it is as general)
    When I use your code, I get the error: Syntax error: expected something between the word '20160831' and ')'



    There is something I just don't see.


    Greetings.

  • Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata


    OK - I can't really help as I don't use Teradata, and never had. Was just going on the comments in your post.


    One final thing, rather than the " marks (Chr(34)), try single quotes - but it's more likely you'll have to CAST the string to a Date type for the DBMS to recognise it. Hopefully someone familiar with Teradata will happen along...


    Code
    ...AND ((MVT_DATE) <= '" & ToDate  & "') Order By...


    (Note single quote before and after "s)

  • Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata


    hi, I used the above, but still the same. It errors like in the previous one and no chance to get this working.
    The strange thing is, that when I pass a Between in the dates, it works fine

    Code
    "WHERE ((TRANSACTION_DATE) Between " & FromDate & " And " & ToDate & ")AND ((BRANCH_NO)= " & branch & ") AND ((ACCOUNT_NO)= " & Acc & ") Order By TRANSACTION_DATE "


    Wonder why the Between is working and not the <=


    Greetings.

  • Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata


    Hi,
    I actually had a more close look ot the column properties. It turns it is set as date (the MVT_DATE) and DECIMAL(9,0) the (Transaction_Date)
    maybe there is the whole thing that when I pass the 20160831 in the (Transaction_Date), it works, but the date format is causing the (MVT_DATE) not to be seen.


    Maybe this can help to figure out what is going on.


    Greetings.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!