473,714 Members | 2,401 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ADO dates and '0001-01-01'

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.Connectio n
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, adLockBatchOpti mistic, adCmdText

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

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

RS.Fields("anyD ate").Value = 0
Debug.Print RS.Fields("anyD ate").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.Connectio n

CN.CursorLocati on = adUseClient
CN.Open "Driver={Cl ient Access ODBC Driver (32-bit)};" & _
"System=**SYSTE M_NAME**;" & _
"DBQ=**CATALOG_ NAME**;" & _
"Uid=**USERNAME **;" & _
"Pwd=**PASSWORD **;" & _
"Commpressi on = 1;" & _
"Signon = 2;" & _
"Blocksize=512; " & _
"Prefetch=1 ;"
End Sub
Nov 12 '05 #1
7 5117

<sm******@yahoo .com> wrote in message
news:40******** *************** ***@posting.goo gle.com...
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.Connectio n
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, adLockBatchOpti mistic, adCmdText

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

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

RS.Fields("anyD ate").Value = 0
Debug.Print RS.Fields("anyD ate").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.Connectio n

CN.CursorLocati on = adUseClient
CN.Open "Driver={Cl ient Access ODBC Driver (32-bit)};" & _
"System=**SYSTE M_NAME**;" & _
"DBQ=**CATALOG_ NAME**;" & _
"Uid=**USERNAME **;" & _
"Pwd=**PASSWORD **;" & _
"Commpressi on = 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
Nov 12 '05 #2
> 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
Nov 12 '05 #3
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.goo gle.com...
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.Connectio n
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, adLockBatchOpti mistic, adCmdText

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

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

RS.Fields("anyD ate").Value = 0
Debug.Print RS.Fields("anyD ate").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.Connectio n

CN.CursorLocati on = adUseClient
CN.Open "Driver={Cl ient Access ODBC Driver (32-bit)};" & _
"System=**SYSTE M_NAME**;" & _
"DBQ=**CATALOG_ NAME**;" & _
"Uid=**USERNAME **;" & _
"Pwd=**PASSWORD **;" & _
"Commpressi on = 1;" & _
"Signon = 2;" & _
"Blocksize=512; " & _
"Prefetch=1 ;"
End Sub

Nov 12 '05 #4
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

Nov 12 '05 #5
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.

Nov 12 '05 #6
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

Nov 12 '05 #7
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"
Jul 16 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3293
by: Colin Steadman | last post by:
I'm a stupid ASP programmer and I dont do Javascript (except for very simple tasks anyway), and I'm in a bit of a predicament. I've used a javascript table sorting script from here: http://www.ipwebdesign.net/kaelisSpace/useful_tableSort.html This works great except it doesn't sort my UK formatted dates properly, and I end up with something like this: Birth Date (dd/mm/yyyy)
3
421
by: kmcnet | last post by:
Hello Everyone and thanks for your help in advance. I am developing an application that utilizes a separate class library for data access to a SQL Server database. One of the tables has several fields defined as DateTime. The data access layer exposes properties for each field in the table. In the instance of datetime fields, the class returns a property of date type. The problem occurs when one of the datetime fields is NULL, which results in a...
1
2993
by: Reza | last post by:
Hi I have a column in my datagrid that can have values of null at times. I am not assigning any value to it, if it is coming from Database empty. Now, the problem is I guess the datetime variables have a default value. Thus shows an undesirable value of 1/1/0001 How do I go about not showing anything when the value is null like this Thanks in advance Reza
0
1031
by: mg | last post by:
I have a WebForm (C#) with a System.Web.UI.WebControls.Calendar I first select the current date, and it's returned correctly I then close the WebForm and reopen it I next select a date other than the current date, and it's returned correctly I then close the WebForm and reopen it
13
1461
by: Scott Hembrough | last post by:
Hello. I have two snippets of code here that are very similar. One works, but the other doesn't. Can someone explain why? Snippet 1: Local "date" variable is set to nothing. Compiles fine, sets date to 1/1/0001 12:00:00 AM. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim x As Date
18
12392
by: Michel Rouzic | last post by:
I can't think of a simple way to print integers with a fix number of digits, in order to obtain something like 0001 for 1 and 0100 for 100 automatically just by specifying in a variable the number of desired digits. I thought about printing to a variable using for example %4d and then replace ' ' by '0', but I don't know if i can print to an array, and then I don't think I can put a variable between % and d to specify the number of...
1
15730
by: pitfour.ferguson | last post by:
My dbase has the start date and end date of each visit. How can I ask Access to list the day of the week of the start (easy), end (easy) and, more importantly, the dates of the visit itself - ie list the dates between the start and end date. Basically I am doing a day of the week analysis and need to know which day of the week is busiest.
3
16258
by: Harish | last post by:
I am buiding an application in ASP.Net 2.0 with C#. After installing corel wordperfect i am adding the reference into the application. Afetr adding reference interop.wordperfect.dll is added to the Bin folder. After creating the object of Wordperfect.Perfectscript i am opening the wordperfect file and then run the wordperfect macro. In development version the application is running without any error. If i publish the website on local...
1
2620
Cyberdyne
by: Cyberdyne | last post by:
Hi Guys I am working on a database that will have a locked auto field with the following characteristics. It has to be a number that will be stored in a table, it needs to have the following format 00-0000 , in the actual table it will start with 06-0001 that being 06 is the year then - 0001 being the first case, from then on it will be 06-0002, 06-0003, 06-0004 and so on when next year hits I want the 06 to change to 07-0001 and so on. The...
8
7634
by: =?Utf-8?B?U2VyZ2V5IFBvYmVyZXpvdnNraXk=?= | last post by:
Hi, I need to validate a textbox to have exactly 4 characters that represent the number 0001 to 9999 (cannot be 0000). The regext I came up with rather long: ^(\d){3} | \d{2}\d | \d\d{2} | \d{3}$ and not easilily extendable should the need arise for say 5 or 6 numbered text.
0
8802
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8711
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9314
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9175
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
5948
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4464
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4726
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3158
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2522
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.