ADO dates and '0001-01-01'

[smcgouga]

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]

<sm******@yahoo.com> wrote in message
news:40**************************@posting.google.c om...

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

I don't know anything about ADO and I know darn little about AS/400 for that
matter. However it sounds to me like you might be better off to store null
rather than 0001-01-01 in your date fields.

Personally, I would only ever want to see 0001-01-01 in a date field if it
was the date that some specific thing actually happened but you seem to be
using it as a placeholder, not to represent a real date. Therefore, it would
make more sense to me to store a null, which means "unknown or not
applicable".

I don't know if ADO recognizes the concept of nulls so I don't know if it
has syntax to let you change a value to null but you might want to
investigate this if my argument has persuaded you.

Rhino

[smcgouga]

> Personally, I would only ever want to see 0001-01-01 in a date field if it

was the date that some specific thing actually happened but you seem to be
using it as a placeholder, not to represent a real date. Therefore, it would
make more sense to me to store a null, which means "unknown or not
applicable".
The database field has been defined as not null and other programs are
expecting the '0001-01-01' value. However I agree that using nulls
may have been the "right" way to do it but too many programs depend on
the value '0001-01-01' now.
I don't know if ADO recognizes the concept of nulls so I don't know if it
has syntax to let you change a value to null but you might want to
investigate this if my argument has persuaded you.

Yes ADO will let you use nulls if the field in the database allows
nulls. You can even trick ADO and force nulls (save to xml, edit
recordset definition, open) but the update will cause an error since
the database field still will not allow nulls.

Cheers
Stu

[Mark Yudkin]

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?)

<sm******@yahoo.com> wrote in message
news:40**************************@posting.google.c om...

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

[Jonathan Bailey]

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]

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]

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]]

sm******@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"