Date Issues between MSDAORA.1 and OraOLEDB.Oracle on standard install - 11g
358921Jun 16 2011 — edited Jun 17 2011<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>