Solved

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

Posted on 2011-02-14
4
412 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:rtay
4 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34892234
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

0
 
LVL 7

Expert Comment

by:lundnak
ID: 34892236
You needs to pass a null value to the date field.  Make sure it isn't an empty set value.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 34892309
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.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 34895594
I think the problem is not with the SQL Server but with the control being used. The DateTimePicker does not support Null as its date value.

Try one of these nullable datetimepickers

http://www.codeproject.com/KB/selection/Nullable_DateTimePicker.aspx

http://www.codeproject.com/KB/selection/NullableDateTimePicker.aspx

http://www.codeproject.com/KB/selection/NDTP_VS2008.aspx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL compatability in SQL 2016 2 31
all records from previous month 6 45
How to use odbc in vb to connect to ms sql 14 37
Get Top Visible Row of Datagridview 6 27
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now