ADO dates and '0001-01-01'

smcgouga@yahoo.com · *nix forums beginner Joined: 15 Mar 2005 Posts: 2

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.

Cheers
Stu

Option Explicit

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset

Private Sub Command1_Click()
Dim sSQL As String
sSQL = "select prikey, anyDate from tbl where prikey = 1"

RS.Open sSQL, CN, adOpenStatic, adLockBatchOptimistic, adCmdText

Debug.Print RS.Fields("anyDate").Value
'"2005-03-13"

RS.Fields("anyDate").Value = dateserial(1,1,1)
Debug.Print RS.Fields("anyDate").Value
'"0100-01-01" 'close: only 100 years out!

RS.Fields("anyDate").Value = 0
Debug.Print RS.Fields("anyDate").Value
'"00:00:00" 'looks promising but updates DB with '1899-12-31

RS.updatebatch

End Sub

Private Sub Form_Load()
Set CN = New ADODB.Connection

CN.CursorLocation = adUseClient
CN.Open "Driver={Client Access ODBC Driver (32-bit)};" & _
"System=**SYSTEM_NAME**;" & _
"DBQ=**CATALOG_NAME**;" & _
"Uid=**USERNAME**;" & _
"Pwd=**PASSWORD**;" & _
"Commpression = 1;" & _
"Signon = 2;" & _
"Blocksize=512;" & _
"Prefetch=1;"
End Sub

Rhino · *nix forums Guru Joined: 08 Feb 2005 Posts: 449

<smcgouga@yahoo.com> wrote in message
news:40e5ab91.0503150321.4d10d38c@posting.google.com...

smcgouga@yahoo.com · *nix forums beginner Joined: 15 Mar 2005 Posts: 2

Mark Yudkin · *nix forums Guru Wannabe Joined: 29 May 2005 Posts: 117

VB6 defines that a date string with a two digit year be windowed to the
1930 - 2029 century. The purpose was to ease Y2K migration, the disadvantage
is that you can't enter dates prior to year 100 - not that these commonly
occur in typical data processing tasks (unless you're an archeologist or a
paleontologist, who has data going back that far?). This is VB6 trying to be
helpful with the 2 digit dates so popularly used prior to the late 1990s,
not a limitation on PC dates, and is clearly documented.

Use the DB2 DATE function to effect the conversion from string to DB2 DATE
in SQL. Another possibility is to use the base Windows APIs to effect
conversion, but this is definitely a lot more work, and will hinder any
subsequent port to VB.NET, which gratuitously changed the internal
representation of dates (to maximize Microsoft's earnings in the consultancy
services?)

<smcgouga@yahoo.com> wrote in message
news:40e5ab91.0503150321.4d10d38c@posting.google.com...

Jonathan Bailey · *nix forums beginner Joined: 17 Mar 2005 Posts: 2

I suppose you could choose to update the lovalue fields in ADO by
setting them to another value - lets say '1899-12-31' then add a
change/add trigger on the as400 file to change any of these dates to
lovalue.

Jonathan

Madcap · *nix forums beginner Joined: 17 Mar 2005 Posts: 1

Can you make the field's default value "0001-01-01" and then pass it a
NULL value from VB? Sorry if this is off base, my DB experience is
mainly in MySQL.

cwahlmeier@data-tronics.c · *nix forums beginner Joined: 14 Jun 2005 Posts: 11

Stu,

Date, Time and Timestamps are the such a pain in ADO. I would suggest
using a command object, but you say you cannot use one.
Next I would suggest that you select the date using a CHAR(anyDate). I
see your comment about fooling the recordset but failing the update...
Thus I wonder if the CHAR will meet the same demise.

Also, what is in your DB2CLI.INI file? There are several settings that
affect the interpretation of the date datatype. Many of them are
dependant on the version of your DB2 Client.

Another question, why doesn't the ADODB.Command object work for you?

Regards,

Craig Wahlmeier

Bob Barrows [MVP] · *nix forums beginner Joined: 16 Jul 2006 Posts: 1

smcgouga@yahoo.com wrote:

Google