• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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
0
chkueh
Asked:
chkueh
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
_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
 
_agx_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
 
chkuehAuthor 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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now