Results 1 to 5 of 5

Thread: SQL 2000 faulty Datetime values

  1. #1
    Join Date
    Aug 2003
    Posts
    4

    SQL 2000 faulty Datetime values

    I have a SQL 2K db with a table having a datetime column.

    I enter data into the table using a VB 6 ADODB Recordset.

    When i do not specify a value for the datetime column and save my ADODB recordset, the table shows a value
    30th December 1899 12:00:00 a.m.

    How do i get rid of this?

    Does a datetime column specified as NOT NULL take some kind of null value ????


  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    How are you setting the value for column in recordset?

    If you have defined the column as not null then check if you have a default defined on it.

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    If your recordset puts 0 or '' for date column then it is saved as 1/1/1900 by sql server.

  4. #4
    Join Date
    Aug 2003
    Posts
    4
    thanx for the reply.

    Actually this problem occurs when i do not enter a value for the column in the recordset i.e. something like

    1. On Error Resume Next
    2. rs("DateCol") = ''
    3. rs.Update

    The above code encounters an error in line 2. but because of line 1. it moves on to execute line 3.

    This is not very elegant coding i aggree but it cant be avoided.

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I believe .net handles date from dec 30 1899.

    see the below links

    http://www-rcf.usc.edu/~anthonyb/itp150/2000.htm

    you can avoid this by hardcoding the datetime value

    1. On Error Resume Next
    2. rs("DateCol") = '01/01/1900'
    3. rs.Update

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •