ADO/SQL Server: datetime column appears to be "1901-1-1 00:00:00" after insertion

Hi.

I'm using ADO connection and recordset to insert records from Table1
to table2.
The database is SQL Server 2005 and the connection is established
before the following the code snippet with
"csvSQL". The code works without any error but the result is not what
i want.
The issue is that when aTIME1 is "" and is inserted to table2, TIME1
of Table2 becomes "1901-1-1 00:00:00".
FYI, TIME1 is of type datetime and allow null. What i expect is when
aTIME1 is "" and inserted to Table2, TIME1 should be null.
Please help. Thanks.

    Set csvRecord = New ADODB.Recordset
    csvRecord.CursorLocation = adUseClient
    csvRecord.CursorType = adOpenDynamic
    csvRecord.LockType = adLockOptimistic

    csvRecord.Open "SELECT * FROM TABLE1 ORDER BY PCVUE_TIME2 ASC",
csvSQL
    totalRecord = csvRecord.RecordCount

    If Not (csvRecord.EOF) And Not (csvRecord.BOF) Then
        csvRecord.MoveLast
        Do Until csvRecord.EOF
            If IsNull(csvRecord("TIME1")) Then
                aTIME1 = ""
            Else
                aTIME1 = csvRecord("TIME1")
            End If
            If IsNull(csvRecord("TIME2")) Then
                aTIME2 = ""
            Else
                aTIME2 = csvRecord("TIME2")
            End If
            If IsNull(csvRecord("DATA1")) Then
                aDATA1 = ""
            Else
                aDATA1 = csvRecord("DATA1")
            End If
            If IsNull(csvRecord("DATA2")) Then
                aDATA2 = ""
            Else
                aDATA2 = csvRecord("DATA2") '*
            End If
            If IsNull(csvRecord("DATA3")) Then
                aDATA3 = ""
            Else
                aDATA3 = csvRecord("DATA3")
            End If
            If IsNull(csvRecord("DATA4")) Then
                aDATA4 = ""
            Else
                aDATA4 = csvRecord("DATA4")
            End If
            If IsNull(csvRecord("DATA5")) Then
                aDATA5 = ""
            Else
                aDATA5 = csvRecord("DATA5")
            End If
            If IsNull(csvRecord("DATA6")) Then
                aDATA6 = ""
            Else
                aDATA6 = csvRecord("DATA6")
            End If
            If IsNull(csvRecord("TIME3")) Then
                aTIME3 = ""
            Else
                aTIME3 = csvRecord("TIME3")
            End If
            If IsNull(csvRecord("DATA7")) Then
                aDATA7 = ""
            Else
                aDATA7 = csvRecord("DATA7")
            End If
            If IsNull(csvRecord("TIME4")) Then
                aTIME4 = ""
            Else
                aTIME4 = csvRecord("TIME4")
            End If


            If InStr(1, aDATA2, "*") > 0 Then
                pointerFound = True
            End If


            If pointerFound And dataReady Then
             sSQL = "INSERT INTO
TABLE2(TIME1,TIME2,DATA1,DATA2,DATA3," & _
                    "DATA4,DATA5,DATA6,TIME3,DATA7,PCVUE_TIME3) VALUES
('" & _
                    aTIME1 & "','" & aTIME2 & "','" & aDATA1 & "','" &
_
                    aDATA2 & "','" & aDATA3 & "','" & aDATA4 & "','" &
_
                    aDATA5 & "','" & aDATA6 & "','" & aTIME3 & "','" &
aDATA7 & "','" & aTIME4 & "')"
             csvSQL.Execute sSQL
            End If

            If pointerFound Then
                dataReady = True
                If totalRecord = csvRecord.AbsolutePosition Then
                    If InStr(aDATA2, "*") <= 0 Then
                        csvRecord("DATA2") = aDATA2 & "*"
                        csvRecord.Update
                    End If
                End If
                csvRecord.MoveNext
            Else
                csvRecord.MovePrevious
            End If
        Loop
    End If
0
albertleng
4/7/2010 2:30:17 AM
vb.general.discussion 1016 articles. 0 followers. Follow

4 Replies
837 Views

Similar Articles

[PageSpeed] 20

"albertleng" <albertleng@gmail.com> schrieb im Newsbeitrag 
news:88507fa7-a896-4c89-bc7d-a312a4da66d9@10g2000yqq.googlegroups.com...
> Hi.
>
> I'm using ADO connection and recordset to insert records from Table1
> to table2.
> The database is SQL Server 2005 and the connection is established
> before the following the code snippet with
> "csvSQL". The code works without any error but the result is not what
> i want.
> The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> of Table2 becomes "1901-1-1 00:00:00".
> FYI, TIME1 is of type datetime and allow null. What i expect is when
> aTIME1 is "" and inserted to Table2, TIME1 should be null.
> Please help. Thanks.
>


Why on earth do you expect TIME1 to be null?
How is aTIME1 declared? (data type?).

If you would read in Online Help about VBs Date data type and its
internal repesentation and about the corresponding data type of
SQL Server then you wouldn't ask this question.

Helmut. 

0
Helmut
4/7/2010 7:33:20 AM
Hello,

> The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> of Table2 becomes "1901-1-1 00:00:00".

This is how SQL Server converts an empty string :

SELECT CAST('' AS SMALLDATETIME) gives 1901-01-01...

> FYI, TIME1 is of type datetime and allow null. What i expect is when
> aTIME1 is "" and inserted to Table2, TIME1 should be null.

Then use NULL rather than an empty string when the value in your CSV file is 
an empty string.

--
Patrice
 


0
Patrice
4/7/2010 8:40:05 AM
On Apr 7, 3:33=A0pm, "Helmut Meukel" <NoS...@NoProvider.de> wrote:
> "albertleng" <albertl...@gmail.com> schrieb im Newsbeitragnews:88507fa7-a=
896-4c89-bc7d-a312a4da66d9@10g2000yqq.googlegroups.com...
>
> > Hi.
>
> > I'm using ADO connection and recordset to insert records from Table1
> > to table2.
> > The database is SQL Server 2005 and the connection is established
> > before the following the code snippet with
> > "csvSQL". The code works without any error but the result is not what
> > i want.
> > The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> > of Table2 becomes "1901-1-1 00:00:00".
> > FYI, TIME1 is of type datetime and allow null. What i expect is when
> > aTIME1 is "" and inserted to Table2, TIME1 should be null.
> > Please help. Thanks.
>
> Why on earth do you expect TIME1 to be null?
> How is aTIME1 declared? (data type?).
>
> If you would read in Online Help about VBs Date data type and its
> internal repesentation and about the corresponding data type of
> SQL Server then you wouldn't ask this question.
>
> Helmut.

Hi. In this project, TIME1 can be null depending on whether there's a
matching time on the equipment to that event.
aTIME1 is declared as a String.
0
albertleng
4/7/2010 12:27:35 PM
On Apr 7, 4:40=A0pm, "Patrice" <http://scribe-en.blogspot.com/> wrote:
> Hello,
>
> > The issue is that when aTIME1 is "" and is inserted to table2, TIME1
> > of Table2 becomes "1901-1-1 00:00:00".
>
> This is how SQL Server converts an empty string :
>
> SELECT CAST('' AS SMALLDATETIME) gives 1901-01-01...
>
> > FYI, TIME1 is of type datetime and allow null. What i expect is when
> > aTIME1 is "" and inserted to Table2, TIME1 should be null.
>
> Then use NULL rather than an empty string when the value in your CSV file=
 is
> an empty string.
>
> --
> Patrice

Thank you so much...

I replaced '' with NULL and now it works.

0
albertleng
4/7/2010 12:36:15 PM
Reply:

Similar Artilces:

ADO/SQL Server: datetime column appears to be "1901-1-1 00:00:00" after insertion
Hi. I'm using ADO connection and recordset to insert records from Table1 to table2. The database is SQL Server 2005 and the connection is established before the following the code snippet with "csvSQL". The code works without any error but the result is not what i want. The issue is that when aTIME1 is "" and is inserted to table2, TIME1 of Table2 becomes "1901-1-1 00:00:00". FYI, TIME1 is of type datetime and allow null. What i expect is when aTIME1 is "" and inserted to Table2, TIME1 should be null. Please help. Thanks. Set csvR...