How to set default value if there is no date for the field, Ms SQL server

Hi, Experts

I have data field for date value and sometimes the value might be null, so what is the suitable default value that i should input when the date is null?

Thanks

chkueh
chkuehAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
if the date text is empty sure i get an error.

What is the error message ?

Should i input "-", "NULL", or "1900" or others...

You can't use "-" or "1900" for a date/time column. They're not valid date values...

Personally, I prefer using NULL to represent "no date entered", rather than a bogus value like 1/1/1900.  But your date/time column must allow nulls.

ie
CREATE TABLE MyTable ( MyDateColumn date/time NULL )
INSERT INTO MyTable (MyDateColumn) VALUES ( NULL )

0
 
Kalpesh ChhatralaSoftware ConsultantCommented:
Check below thread there is sample for SQL Default Column

http://www.blackwasp.co.uk/SQLDefaultColumns.aspx
0
 
Kalpesh ChhatralaSoftware ConsultantCommented:
Check also below thread there is sample for default value by script

http://stackoverflow.com/questions/92082/add-column-with-default-value-to-existing-table-in-sql-server
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
Loganathan NatarajanLAMP DeveloperCommented:
You can check either the value is null , then insert the default value. Otherwise, you can set it as default in the table definition so that if there is null to the field, it will insert the default value..
0
 
_agx_Commented:
so what is the suitable default value that i should input when the date is null?

Can I ask why you need to insert a default at all? NULL is a valid value for date/time columns. Personally I prefer it, over using bogus dates.
0
 
chkuehAuthor Commented:
Hi agx....i mean if the the value is empty...if the date text is empty sure i get an error. So, i need to know what is the suitable value for me to insert default value. Should i input "-", "NULL", or "1900" or others...

Thanks
0
 
chkuehConnect With a Mentor Author Commented:
Thanks agx...for your idea...

from my vb.net code, i put TextA.text=DBNull.Value

Thanks

0
 
_agx_Commented:
put TextA.text=DBNull.Value

Yep, that'll work. Sorry, I didn't realize you were asking how to do it from the vb.net side, not the db.
0
 
chkuehAuthor Commented:
this is a good link to solve my problem...http://forums.asp.net/t/1070890.aspx/1
0
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.