Running a SELECT against the AS400 using the IBMDA400 OleDb provider appears to return dates as string values, SSRS just laughs at you when you try and apply a date format to the field. I've tried a simple CAST in the SELECT to no avail.
How can I get an actual DBTYPE_DBDATE struct back from the iSeries OleDb provider?
I should mention that the dates in question are all being returned by a UDF with a type of DATE. IBM appears to map DATE type into a DBTYPE_STR OleDb type.
The field(s) in the table(s) are probably not defined as a date type. You will need to convert them using the DATE
function as part of the query.
You can use the DSPFFD
command, the Navigator, or query the SYSIBM.SQLCOLUMNS table to view the field definitions.
After further testing with the IBMDA400 provider I found the Convert Date Time To Char property hidden away in the OLE DB Technical Reference installed as part of the Programmer's Toolkit with Access. The default value is TRUE. Set Convert Date Time To Char=FALSE in the connection string or properties to disable this 'feature'.
Here's a quick VBA test:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=IBMDA400;Data Source=...;User ID=...;Password=...;Convert Date Time To Char=FALSE"
Set rs = cn.Execute("SELECT DATE(NOW()) FROM SYSIBM.SYSDUMMY1")
MsgBox "Returned ADO type: " & rs.Fields(0).Type
See MSDN: DataTypeEnum for the possible ADO data types. adDBDate is 133.
Convert Date Time To Char
Specifies conversion of DB2 for IBM i Date, Time, and Timestamp data types to corresponding PC data types and vice versa.
Settings and Return Values
Sets or returns one of the following string values. The default value is "TRUE".
"TRUE"
DB2 for IBM i Date, Time, and Timestamp data types are treated as character strings. When reading IBM i data, these values are converted to character strings. When writing data to the system, character strings are expected as input for these values. The supported character string format for the Date data type is ISO format: yyyy-mm-dd. The supported character string format for the Time data type is the earlier version of the ISO format: hh.mm.ss. The supported character string format for the Timestamp data type is: yyyy-mm-dd-hh.mm.ss.nnnnnn.
"FALSE"
DB2 for IBM i Date, Time, and Timestamp data types will be converted to PC Date, Time, and Timestamp data types. Care should be taken when using this value in an environment that only supports the Variant Date data type (such as Visual Basic). You may encounter unexpected errors due to truncation or overflow caused by the limitations of the Variant Date data type.
Following are additional considerations when Convert Date Time To Char is FALSE.
Remarks
This custom property is available on the ADO connection object. The property is read/write when the connection is closed and read-only when the connection is open.
Delphi example
<connection>.Provider := 'IBMDA400';
<connection>.Properties('Convert Date Time To Char') := "TRUE";
OR
<connection>.Open('Provider=IBMDA400;Data Source=SystemA;Convert Date Time To Char =TRUE', 'Userid', 'Password');
PowerBuilder example
<connection>.Provider = "IBMDA400"
SetProperty(<connection>), "Convert Date Time To Char", "TRUE")
OR
<connection>.Open("Provider=IBMDA400;Data Source=SystemA;Convert Date Time To Char=TRUE", "Userid", "Password")
Visual Basic example
<connection>.Provider = "IBMDA400"
<connection>.Properties("Convert Date Time To Char") = "TRUE"
AND/OR
<connection>.Open "Provider=IBMDA400;Data Source=SystemA;Convert Date Time To Char=TRUE", "Userid", "Password")