Link to home
Start Free TrialLog in
Avatar of schwientekd
schwientekdFlag for United States of America

asked on

Insert NULL or blank value in smalldatetime field SQL 2005

I'm writing some code in VB.NET which inserts some values into a table.  I'm getting data from one table and inserting it into another table.  There is a date field which I want to insert a NULL or blank value if it is NULL in the source table.  As is it will insert the date 1/1/1900.  Here is my code, which I have just summarized.

For Each dr as DataRow.....

Dim myDate = dr.item(myDT.myDateColumn)

If IsDBNull(myDate) Then
myDate = ""
End If

<define connection variables....>

INSERT INTO dbo.TableName (MyDateField)  VALUES (myDate)

Avatar of kaufmed
Flag of United States of America image

Don't set it to empty string; rather set it to DBNull.Value. I think you would be safe in removing your "if" logic, since if it is DBNull, then that is what you want to insert.
Avatar of schwientekd


I tried it both ways using the if statement and removing it.  Both result in inserting a 1/1/1900 date.  The field I'm inserting into is a date of birth field so I either need a value or nothing at all to be inserted.
Do you have a "default value" set on the table?
No, there is no default value for that field.
To confirm, what you tried was something similar to this?
For Each dr as DataRow.....

Dim myDate = dr.item(myDT.myDateColumn)

<define connection variables....>

INSERT INTO dbo.TableName (MyDateField)  VALUES (myDate)


Open in new window


What is the type defined as in the source table that this column comes from?
Avatar of PagodNaUtak
Flag of Philippines image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial