smcgouga@yahoo.com wrote:
> Visual Basic 6. ADO 2.8
>
> I have an as400 DB2 V5R1 datasource. Dates are defined as *ISO format
> and have a range from '0001-01-01' to '9999-12-31'. I am trying to
> update a date field on the database with a value of '0001-01-01'
> (*LOVAL) The problem is that I need to use ADO cursors and can not
> use the SQL update command:
> "update tbl set dateField = '0001-01-01 where ..." (note this works no
> problem)
>
> The problem stems from the limitation on PC dates. the lowest value a
> PC date can have is '0100-01-01'. When ADO reads a value of
> '0001-01-01' it fills an ADO recordset with "00:00:00". I can deal
> with this by formatting on screen.
>
> However I am not able to set an ADO field to '0001-01-01' since it is
> not a valid PC date. How can I set a date field back to '0001-01-01'
> using updateBatch or update via ADO?
>
> Ive tried saving the ADO RS to XML to fiddle the schema to change the
> date field to a character field so that it would accept "0001-01-01".
> This works and fools the recordset but will not update the database on
> an update/updatebatch!
>
> Could there be a workaround using triggers or a translation DLL? It
> would be much better if I could keep the solution within my VB code.
>
What is the issue with using a SQL statement? Are you aware that under the
covers, ADO is constructing and executing a sql statement to perform the
update? Why can't you just create the sql statement yourself?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"