How to convert blank datetime field to NULL again? SQL 2008 backend of VB Windows App

I have a windows application written in VB from Visual Studio.  SQL 2008 Backend.  For datetime fields, if the user removes the date after they have entered one, it does not remove the date..... I understand it wants a valid date, but how can I delete a date that was entered by mistake?  Returning the field to NULL.
Who is Participating?
ErnariashConnect With a Mentor Commented:

You can try inline T-SQL to accomplish the same...hope this help you...
if your parameter is not a date then insert or update with nulls
 CASE WHEN IsDate(@yourdatetimeParam)=0 THEN NULL ELSE @yourdatetimeParam END
UPDATE ur_table
SET datetime_col_name = CASE WHEN IsDate(@yourdatetimeParam)=0 THEN NULL ELSE @yourdatetimeParam END
WHERE ....

Raja Jegan RSQL Server DBA & ArchitectCommented:
This should update it back to NULL:

UPDATE ur_table
SET datetime_col_name = NULL
WHERE pk = pk_value
update table set fieldname = NULL
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

sassymackeAuthor Commented:
I'm sorry... I should have clarified a bit better.

I want the program to change the field value to NULL if it is empty, anytime it's empty.  Possibly this does it, I'm not sure how I'd use it....

I use the tableadapters in Visual Studio.  Is there a way to insert code on save event that checks for "" and then changes to NULL?

I think you answer yourself to insert code on save event that checks for "" and then changes to NUL&
Or if you are using Store procedure. ( I am assuming you field is Nullable)
you can check for you string and converted there.

if (@yourdatetimeParam = ) set @yourdatetimeParam = null
//This one will check for any non datetime including empty strings
if (IsDate(@yourdatetimeParam)=0) set @yourdatetimeParam = null
sassymackeAuthor Commented:
I'm not using a stored procedure.  What code would I insert on save event?  I've tried to get something to work but had no luck.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.