MS Access-MS Excel integration corrupt dates

[tranchemontaigne]

Environment: Windows XP, MS Office 2000

Scenario: I have a code block that takes the results of a recordset and stores the values within an array of type variant. Results from the array are then written to cells in an MS Excel worksheet.

Problem: When dates are retrieved from the array and written to MS Excel, any dates that preceed 1/1/1930 are altered by adding 100 years. For example, 6/24/1912 becomes 6/24/2012 when written to MS Excel.

Question: Does anyone have a solution or tip I could use to overcome this bizarre behavior?

 

[PHV]

Use a four digit year (ie include the century)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Why would you use ambiguous data?

Check out "So what happens when you enter a Date or Time into Excel?" in faq68-5827.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tranchemontaigne]

Here's the solution

I altered the function that generates the dynamic SQL to include the following line of code:

"NVL(TO_CHAR(DECD_BRTH_DT,'MM/DD/YYYY'),' ') AS DOB"

Dates in Oracle are stored as a date data type. Regardless of how dates are stored, Oracle seems to always return date values as a string. Oracle queries can be fed 4 digit years, but Oracle date results are by default returned as strings with 2 digit years (e.g. '31-DEC-29'). MS Excel 'automagic' then dots the T's and crosses the I's to convert 31 December 1929, that became 31-DEC-29 in the query result set, into December 31 2029 when received into MS Excel.

All of this was confounded by my lack of Oracle experience to even anticipate this problem.