1 Reply Latest reply: Jun 16, 2011 11:35 PM by gdarling - oracle RSS

    Date Issues between MSDAORA.1 and OraOLEDB.Oracle on standard install - 11g

    358921
      <B>I was attempting to upgrade from MSDAORA.1 to OraOLEDB.Oracle in IIS and encountered an issue with dates.

      Doing exactly the same query with the different providers, yielded the following results:</B>

      Set DBConn = Server.CreateObject("ADODB.Connection")
      DBConn.Open "Provider=MSDAORA.1; Data Source=TEST11G2; User Id=scott; Password=tiger", "scott", "tiger"
      if DBConn.Errors.Count > 0 Then
      CloseAndRedirect("/Errors/ServerUnavailable.htm")
      end if

      sql="SELECT TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'MON-YY')), 'DD-MON-YY') start_date, " & vbCRLF _
      & "TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'MON-YY')) - 1, 'DD-MON-YY') end_date " & vbCRLF _
      & "FROM dual " & vbCRLF

      Response.Write(replace(sql, vbCRLF, "Break Tag")
      Set newsrst = DBconn.Execute (sql)
      Response.Write("START:" & Newsrst.fields("start_date") & " END:" & Newsrst.fields("end_date"))


      <B>Returns</B>

      SELECT TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, - '0'), 'MON-YY')), 'DD-MON-YY') start_date,
      TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, - '0' + 1), 'MON-YY')) - 1, 'DD-MON-YY') end_date
      FROM dual

      START:01-JUN-11 END:30-JUN-11


      <B>As expected, while:</B>

      Set DBConn = Server.CreateObject("ADODB.Connection")
      DBConn.Open "Provider=OraOLEDB.Oracle;Data Source=TEST11G2;User ID=scott;Password=tiger"

      if DBConn.Errors.Count > 0 Then
      CloseAndRedirect("/Errors/ServerUnavailable.htm")
      end if

      sql="SELECT TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'MON-YY')), 'DD-MON-YY') start_date, " & vbCRLF _
      & "TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'MON-YY')) - 1, 'DD-MON-YY') end_date " & vbCRLF _
      & "FROM dual " & vbCRLF

      Response.Write(replace(sql, vbCRLF, "Break Tag")
      Set newsrst = DBconn.Execute (sql)
      Response.Write("START:" & Newsrst.fields("start_date") & " END:" & Newsrst.fields("end_date"))


      <B>Returns</B>

      SELECT TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, - '0'), 'MON-YY')), 'DD-MON-YY') start_date,
      TO_CHAR(TO_DATE('01-' || TO_CHAR(ADD_MONTHS(SYSDATE, - '0' + 1), 'MON-YY')) - 1, 'DD-MON-YY') end_date
      FROM dual

      <FONT color=red>START:11-JUN-01 END:10-JUL-01</FONT color=red>


      <B>Has anyone else seen this and is there a solution?</B>
        • 1. Re: Date Issues between MSDAORA.1 and OraOLEDB.Oracle on standard install - 11g
          gdarling - oracle
          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 ?
          http://download.oracle.com/docs/cd/B19306_01/win.102/b14311/using.htm

          Hope it helps,
          Greg



          <snippet>
          Date Formats

          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.Parameters.Append pDate
          objCmd.CommandText = _
          "SELECT * FROM EMP WHERE HIREDATE > ?"
          objCmd.ActiveConnection = objCon
          objCmd.CommandType = adCmdText
          pDate.Value = "06/15/1981"
          Set objRst = objCmd.Execute

          ...
          </snippet>