Oracle dates in ADO

[Guest]

I am having issues getting a sql statement w/ a date parameter to work from
ADO connecting to an oracle DB. I am putting the date in the oracle date
format (dd-mmm-yy) and it doesn’t seem to be working. I have tried several
things including: to_date oracle function, formatting the string as date
instead of string, hard-coding a date, and various combinations of these with
no success. Any help would be appreciated.

General info: the connection works ( I can get any info except w/ the date
parameter)
priorDt = ’04-mar-07’ (currently a formatted string)

Sub:
Private Sub GetInitialData(ByRef cn As ADODB.Connection, ByRef rsPriorExcep
As ADODB.Recordset, ByVal priorDt As String)
' Pulls initial data from into recordsets (for both prior and current)
On Error GoTo ErrorHandler

Dim sqlPrior As String
sqlPrior= "Select * from tblExceptions " & _
"where source_date = '" & priorDt & "' and category = 'C'
and error_type = 'UPD' "
Debug.Print sqlPrior
Set rsPriorExcep = New ADODB.Recordset

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
.CommandText = sqlPrior
.CommandType = adCmdUnknown
.ActiveConnection = cn
Set rsPriorExcep = .Execute
End With
Debug.Print cmd.CommandText
Debug.Print rsPriorExcep!error

Exit Sub

ErrorHandler:
Excel.Application.ActivateMicrosoftApp xlMicrosoftAccess
MsgBox "Error connecting to REVO." & Chr(10) & Err.Number & ": " &
Err.Description, vbOKOnly, "Error connecting"
End Sub

 

[Stefan Hoffmann]

hi,

I am having issues getting a sql statement w/ a date parameter to work from
ADO connecting to an oracle DB. I am putting the date in the oracle date
format (dd-mmm-yy) and it doesn’t seem to be working.

Using the To_Char() method with another date format works so as passthrough:

SELECT To_Char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS SERVER_NOW
FROM DUAL;

Dim sqlPrior As String
sqlPrior= "Select * from tblExceptions " & _
"where source_date = '" & priorDt & "' and category = 'C'

Create a function like

Option Compare Database
Option Explicit

Public Function DateToOracleStr(ADate As Date, _
Optional ADelimiter As String = "'" _
) As String

DateToOracleStr = ADelimiter & _
Format(ADate, "yyyy-mm-dd hh:nn:ss") & _
ADelimiter

End Function

with

Dim sqlPrior As String

sqlPrior= "Select * from tblExceptions " & _
"where source_date = " & DateToOracleStr(priorDt) & " " & _
"and category = 'C'"

mfG
--> stefan <--

 

[onedaywhen]

I am having issues getting a sql statement w/ a date parameter to work from ADO connecting to an oracle DB. I am putting the date in the oracle date
format (dd-mmm-yy) and it doesn't seem to be working. I have tried several
things including: to_date oracle function, formatting the string as date
instead of string, hard-coding a date, and various combinations of these with
no success.

Rather than dynamic/ad hoc SQL text, try using an ADO parameter
object. It is the job of the OLE DB provider to take a strongly-typed
date from the 'front end' (e.g. VBA) and 'translate' this into a
strongly-type date in the 'back end' (e.g. DBMS server).

Jamie.

--