Converting Oracle date to Access Data format

Question

Good morning,

I a linking to an Oracle Data (table) through ODBC. I do not have any control over how this table is design or the data is stored.

However, the data column in coming in as text data type in the following format, example: 20120110

I used the following code to convert the date in access:

CDate(Right([Example.MY_DATE],2) & "/" & Mid([Example.MY_DATE],5,2) & "/" & Left([Example.MY_DATE],4))

This converts the date to: 01/10/2012

Is this the right way to get this going?

I am getting mix results, meaning my result set on my query is not accurate, I just want to make sure my conversion method is right.

Example:

`SELECT

CDate(Right([Example.MY_DATE],2) & "/" & Mid([Example.MY_DATE],5,2) & "/" & Left([Example.MY_DATE],4)) AS Format_Date

WHERE (((CDate(Right([xample.MY_DATE],2) & "/" & Mid([xample.MY_DATE],5,2) & "/" & Left([xample.MY_DATE],4)))>#01/09/2012#)

ORDER BY

CDate(Right([Example.MY_DATE],2) & "/" & Mid([Example.MY_DATE],5,2) & "/" & Left([Example.MY_DATE],4))`

Thanks everyone!

Matt Donnan Matt Donnan · Accepted Answer · 2012-01-10T16:28:44

Have you tried using the 'DateSerial' function instead:

Date = DateSerial(Left(MyDate, 4), Mid(MyDate, 5, 2), Right(MyDate, 2))

Date Serial should convert the date to the default format for your environment e.g in the English format rather than the American format etc.

So the SQL may look like this:

SELECT DateSerial(Left(MyDate, 4), Mid(MyDate, 5, 2), Right(MyDate, 2)) AS Format_Date
FROM Tablename
WHERE DateSerial(Left(MyDate, 4), Mid(MyDate, 5, 2), Right(MyDate, 2)) > #01/09/2012#
ORDER BY DateSerial(Left(MyDate, 4), Mid(MyDate, 5, 2), Right(MyDate, 2))

Converting Oracle date to Access Data format

1 Answers