Share via

SAP DATS column containing the value "a.b.c.d" ??? (Problem when SAP ADO is used ..)

  • Article

While going through the data stored in one of the tables in SAP, we saw a value "a.b.c.d" stored in a DATS column! How could that be? A DATS column is supposed to store a Date value, right?

Well, it seems that SAP does not enforce any validation on the value being stored in a DATS column (other than the simplistic validation done by the SAP GUI). Therefore, any random junk can be stored in a DATS column. This might lead to a problem using the SAP ADO Provider ... here's how:

You use the SAP ADO Provider to select data from a table T, (having a DATS column), in which one row contains junk in this DATS column. At runtime, when you execute:

SELECT * FROM T

The ADO Provider at runtime, will try converting the data returned from SAP to a DateTime value. This, as can be expected, will cause an error/exception to be thrown.

Luckily, there is a workaround:

SELECT * FROM T OPTION 'DisableDataValidation'

When the above query is used, the ADO Provider does not attempt to convert values in DATS and TIMS columns to a DateTime object, but instead exposes it as a String.