<div>
<b>Full tested sample for you -</b><br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42348865-1"><span>CREATE TABLE tstDefault</span>
<span>(</span>
<span>	ID INT</span>
<span>	,dateCol DATETIME NOT NULL DEFAULT '1900/01/01'</span>
<span>)</span>
<span>GO</span>
<span></span>
<span></span>
<span>INSERT INTO tstDefault(ID) VALUES (1)</span>
<span></span>
<span>SELECT * FROM tstDefault</span>
</code></pre>
<b>OUTPUT</b><br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348865-2"><span>/*------------------------</span>
<span>SELECT * FROM tstDefault</span>
<span>------------------------*/</span>
<span>ID          dateCol</span>
<span>----------- -----------------------</span>
<span>1           1900-01-01 00:00:00.000</span>
<span></span>
<span>(1 row(s) affected)</span>
</code></pre>
</div>


<div>
Just adding to my previous solution .. If you insert date..it will look like below -<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42348870-1"><span>INSERT INTO tstDefault(ID,dateCol) VALUES (2,GETDATE())</span>
<span></span>
<span>SELECT * FROM tstDefault</span>
<span>/*------------------------</span>
<span>SELECT * FROM tstDefault</span>
<span>------------------------*/</span>
<span>ID          dateCol</span>
<span>----------- -----------------------</span>
<span>1           1900-01-01 00:00:00.000</span>
<span>2           2017-10-31 00:18:41.103</span>
<span></span>
<span>(2 row(s) affected)</span>
</code></pre>
</div>


<div>
If you have existing column just use below -<br />
<br />
<b>ALTER TABLE tstDefault ADD CONSTRAINT DF_DateDeafult DEFAULT '1900/01/01' FOR datecolumn;</b>
</div>


<div>
@Pawan, you really should know that: Use an unambiguous date format for literals like 19000101, which is every time correctly interpreted and not depended on the systems DATEFORMAT.
</div>


<div>
How can I insert only year and month only when there is no day information available in datetime field
</div>


<div>
DATEFROMPARTS<br />
<br />
You can create date using datefromparts.. If the day is not given then you can provide first day of the month like below ( 01 ) as day.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348883-1"><span>/*------------------------</span>
<span>SELECT DATEFROMPARTS('2016','01','01')</span>
<span>------------------------*/</span>
<span></span>
<span>----------</span>
<span>2016-01-01</span>
<span></span>
<span>(1 row(s) affected)</span>
</code></pre>
</div>


<div>
Sorry , I don't get datefromparts ?<br />
<br />
Currently the field type available is date or datetime.<br />
If I choose either or and try to enter just YYYY or YYYY-MM it gives me error.<br />
<br />
It forces me to enter YYYY-MM-DD format only.
</div>


<div>
<b>Only year and month cannot be inserted in datetime column so we have to convert it to a date. </b>Thats why in these cases we can use DateFromParts.<br />
<br />
Or there are other methods also to create a date from year and month but for that we have to add day . without that it would not be possible to create a date.
</div>


<div>
Vitor-<br />
if you use that you will get below error<br />
<br />
Msg 242, Level 16, State 3, Line 1<br />
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.<br />
<br />
Casting is required in this case.<br />
<br />
This will work...<br />
SELECT Id,ISNULL(CAST(dateCol AS VARCHAR),'0000-00-00') FROM tstDefault11
</div>


<div>
Vitor - I agree with you if the road you're going down (I'm guessing this is correct) is to store the absence of a value as NULL in the database, and let the presentation layer apply whatever business logic is needed to present/do whatever it needs to do..
</div>


<div>
Pawam, that was more to show the author that he don't need to store the value as he wants. If I wanted to be more precise I wouldn't use CAST but CONVERT or FORMAT functions to return the date in the YYYY-MM-DD format.<br />
<br />
Jim, yes, that's the idea. NULL is absence of value and the author can workout what to present when found a NULL value.
</div>


<div>
Vitor - Its Pawan not Pawam
</div>


<div>
Btw, we are assuming that author is storing the dates in a proper date datatype column but if he's storing it in a varchar column then no conversion needed.<br />
<br />
Went to read back the question and the 2nd and 3rd points are really scary if the idea is to use a single field to store it:<br />

<div class="indent">
1. Some records no date at all but I don't want to leave datetime field empty<br />
2. Some records only year and month available<br />
3. Some records only year available.
</div>
</div>


<div>
If some records do have incomplete date then you can store such values in char columns only. Thus the default 0000-00-00 is easily achievable.<br />
<br />
Of course, incomplete date requires special processing in the code.
</div>


<div>
Thank you everyone for your valuable input.
</div>


<div>
<div class="content wysiwyg-content">
I want the SQL Server default date to be 0000-00-00, as for some records I have following situations and not sure how can I tackle the default date for every record insert<br />
<br />
1. Some records no date at all but I don't want to leave datetime field empty<br />
2. Some records only year and month available<br />
3. Some records only year available.<br />
<br />
How can I default the missing information to just 0000 or 00 automatically whenever there is a records inserted.<br />
<br />
Thanks
</div>
</div>


I want the SQL Server default date to be 0000-00-00, as for some records I have following situations and not sure how can I tackle the default date for every record insert

1. Some records no date at all but I don't want to leave datetime field empty
2. Some records only year and month available
3. Some records only year available.

How can I default the missing information to just 0000 or 00 automatically whenever there is a records inserted.

Thanks

Default Date SQL Server 2008

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.