Excel VBA and Oracle date formatin WHERE

    I'm creating a connection in Excel to Oracle tables. I have my SELECT statement done for the most part but can't seem to get the hardcoded date parameter to work I've tried #1/01/2011# and '1/01/2011' as well.


    ...strSQL = strSQL & " ,lr.cntry_cd"
    strSQL = strSQL & " FROM long_range lr"
    strSQL = strSQL & " )"
    strSQL = strSQL & "WHERE lr.need_dt >=1/01/2011"

    Also, any ideas on where I can go to find out how to pull my data from two different Oracle server locations? I can make the connections but need help with the SQL syntax which includes LEFT and INNER joins and IIF statements.

    I'm getting "Automation error 80040e14" with the above on the rst.Open line. If I remove the WHERE clause completely, everything works so I know my connection is good.

    Thanks!

    Re: Excel VBA and Oracle date formatin WHERE


    Hi,

    this is how I use it:

    Code
    ...
strSQL = strSQL & " WHERE (LOCAL_TIMESTAMP>=TO_DATE('" & strStart & "', 'yyyy mm dd hh24:MI')"
strSQL = strSQL & " AND LOCAL_TIMESTAMP<TO_DATE('" & strEnd & "', 'yyyy mm dd hh24:MI')"
...



    Try the TO_Date function from oracle with your date as string.
    Hope this helps.

    Re: Excel VBA and Oracle date formatin WHERE


    Thanks for the reply. Are you saying that I HAVE to use the TO_DATE function to just tell the query I want to use a specific date? The field is already formatted the way I want it (as date) so I'm not quite sure if this will work. I've tried it and it doesn't error, I just get no data at all when it's done running. I've tried both of these.

    strSQL = strSQL & " WHERE need_dt>=TO_DATE('" & strNeedDt & "', 'mm/dd/yyyy')"
    strSQL = strSQL & " WHERE need_dt>=TO_DATE('1/1/2011')"

    Thanks for the help, new at Excel SQL stuff.

    Toni

    Re: Excel VBA and Oracle date formatin WHERE


    Got it! Thanks for your help.

    strSQL = strSQL & " WHERE need_dt >=to_date('01/01/2010','mm/dd/yyyy')"

Participate now!

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

Register Yourself Login