Link to home
Avatar of rtay
rtayFlag for United States of America

asked on

SQL Server table date field to delete a date and accept a null or blank

I have a VB.Net edit form with a date field that is optional.  Somtimes a date will be added and at other times the user will delete the date and leave it blank.  The sql field is set to accept nulls and is fine if it is left blank.  When I add a date and then delete a date, I get a sql error that the date format is not correct.  how do I get the table to accept the blank as a null again?
Avatar of Sharath S
Sharath S
Flag of United States of America image

What is your error? You can add blank value to the date field.
declare @table table(col datetime)
insert @table values (null),(GETDATE()),('')
select * from @table
/*
col
NULL
2011-02-14 14:04:47.063
1900-01-01 00:00:00.000
*/

Open in new window

Avatar of lundnak
lundnak

You needs to pass a null value to the date field.  Make sure it isn't an empty set value.
SQL Server does implicit data type conversions of strings to dates/datetimes, but does not convert empty strings to NULL.

Check out DBNull.Value. Eg you set Object.Datetimefield = DBNull.Value in VB.NET before saving.

Bye, Olaf.
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS 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