Avatar of schwientekd
schwientekd
Flag 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)

Next
Visual Basic.NETMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
PagodNaUtak

8/22/2022 - Mon
kaufmed

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.
schwientekd

ASKER
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.
kaufmed

Do you have a "default value" set on the table?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
schwientekd

ASKER
No, there is no default value for that field.
kaufmed

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)

Next

Open in new window

kaufmed

P.S.

What is the type defined as in the source table that this column comes from?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
PagodNaUtak

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.