This content has been marked as final. Show 1 reply
I'm having a hard time figuring out exactly what you're describing, but given that it's something to do with date format, have you already checked the oraoledb docs, specifically the "date formats" section ?
Hope it helps,
The date format for the Oracle session cannot be set using the ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, date formats are controlled by the Regional Settings properties in Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.
For Oracle Provider for OLE DB, if the Connection property UseSessionFormat is FALSE, which is a default value, then NLS_DATE_FORMAT is fixed for the session to 'YYYY-MM-DD HH24:MI:SS' by the provider. If you pass the date to Oracle Database as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format. If UseSessionFormat is TRUE, then NLS_DATE_FORMAT is not fixed by Oracle Provider for OLE DB and the default session NLS_DATE_FORMAT is used. For example:
SELECT * FROM EMP WHERE HIREDATE > '1981-06-15 17:32:12'
To use a different format, you need to use the SQL function, TO_DATE(), to specify the format for dates passed as strings. For example:
SELECT * FROM EMP WHERE HIREDATE > TO_DATE('15-JUN-81', 'DD-MON-YY')
However, for dates passed as parameters, the date format is controlled by ADO, which is controlled by the Regional Settings in Windows Control Panel. In this case, TO_DATE() should not be used. For example:
Private Sub Command1_Click()
Dim objCon As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRst As New ADODB.Recordset
Dim pDate As New ADODB.Parameter
objCon.Provider = "OraOLEDB.Oracle"
objCon.Open "MyOraDb", "scott", "tiger"
Set pDate = objCmd.CreateParameter("pDate", adDate, adParamInput)
objCmd.CommandText = _
"SELECT * FROM EMP WHERE HIREDATE > ?"
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText
pDate.Value = "06/15/1981"
Set objRst = objCmd.Execute