0

I have a VBA/ADODB code that connects Excel to Oracle DB and runs an SQL Query. The problem is that the recordset in Excel shows Oracle DATE fields as adDBTimeStamp (135) even though in Oracle they are stored as simple dates. Basically i want to only import the DD.MM.YYYY parts to Excel. Is it possible to change ADODB field types in recordset before importing it to Excel? I know i could change the format with VBA after importing but i dont want to do that.

The code looks like this:

query = "SELECT NAME,DATE1,DATE2,DATE3 from MY_TABLE"
rs.Open query, Con
Sheets("data_source").Range("A2").CopyFromRecordset rs
Con.Close
user1810355
  • 135
  • 1
  • 6
  • 1
    I think it might be easier to just change the formatting of Excel column to a simple date, wouldn't it? –  Feb 17 '15 at 13:09
  • But then you cannot use it in COUNTIFS formulas (which is something i need) unless you change the format with VBA – user1810355 Feb 17 '15 at 13:17
  • yeah, change it with VBA macro. A quick sub that iterates the column and builds a date in a desired format would be a good start. –  Feb 17 '15 at 17:02

1 Answers1

0

Take care, the Oracle DATE type stores date and time up to seconds. If you want to get only the date part of datas, simply use the trunc function in your query:

query = "SELECT NAME,trunc(DATE1),trunc(DATE2),trunc(DATE3) from MY_TABLE"

Nicolas FATOUT
  • 1