Default Date SQL Server 2008

Guru Ji
Guru Ji used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
You can create a default like below

ALTER TABLE yourTable
  ADD COLUMN yourDate DATETIME NOT NULL DEFAULT  '1900/01/01'

you should use  '1900/01/01' as default date not 0000.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Full tested sample for you -

CREATE TABLE tstDefault
(
	ID INT
	,dateCol DATETIME NOT NULL DEFAULT '1900/01/01'
)
GO


INSERT INTO tstDefault(ID) VALUES (1)

SELECT * FROM tstDefault

Open in new window

OUTPUT

/*------------------------
SELECT * FROM tstDefault
------------------------*/
ID          dateCol
----------- -----------------------
1           1900-01-01 00:00:00.000

(1 row(s) affected)

Open in new window

Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
0000-00-00 is an invalid date, it cannot be stored in a date field.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Just adding to my previous solution .. If you insert date..it will look like below -

INSERT INTO tstDefault(ID,dateCol) VALUES (2,GETDATE())

SELECT * FROM tstDefault
/*------------------------
SELECT * FROM tstDefault
------------------------*/
ID          dateCol
----------- -----------------------
1           1900-01-01 00:00:00.000
2           2017-10-31 00:18:41.103

(2 row(s) affected)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
If you have existing column just use below -

ALTER TABLE tstDefault ADD CONSTRAINT DF_DateDeafult DEFAULT '1900/01/01' FOR datecolumn;
ste5anSenior Developer

Commented:
@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.

Author

Commented:
How can I insert only year and month only when there is no day information available in datetime field
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
DATEFROMPARTS

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.

/*------------------------
SELECT DATEFROMPARTS('2016','01','01')
------------------------*/

----------
2016-01-01

(1 row(s) affected)

Open in new window

Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
>>How can I insert only year and month only when there is no day information available in datetime field

You cannot, you need to insert a valid date. You could always insert 1 as the day and ignore the day in the processes that are using that date.

Author

Commented:
Sorry , I don't get datefromparts ?

Currently the field type available is date or datetime.
If I choose either or and try to enter just YYYY or YYYY-MM it gives me error.

It forces me to enter YYYY-MM-DD format only.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Only year and month cannot be inserted in datetime column so we have to convert it to a date. Thats why in these cases we can use DateFromParts.

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.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>Some records no date at all but I don't want to leave datetime field empty
I highly *** HIGH - LEE *** recommend revisiting this statement, as a NULL date value is always better then a 'dummy' date value when a value truly does not exist, just in case some junior developer doesn't understand this concept well and tries to pull off date math where the dummy value generates a bad result.

imo Eric has correctly answered your questions twice now.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
As Jim, I also recommend you to use NULL values for non date introduced. You can then work with the NULL values when you want to show the DATE as '0000-00-00' with the ISNULL function:
SELECT ColumnID, Description, ISNULL(DateColumn, '0000-00-00') as MyDateFormat
FROM TableName

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Vitor-
if you use that you will get below error

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Casting is required in this case.

This will work...
SELECT Id,ISNULL(CAST(dateCol AS VARCHAR),'0000-00-00') FROM tstDefault11
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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..
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
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.

Jim, yes, that's the idea. NULL is absence of value and the author can workout what to present when found a NULL value.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Vitor - Its Pawan not Pawam
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Apologizes, Pawan.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
:)
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
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.

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:
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.
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.

Of course, incomplete date requires special processing in the code.

Author

Commented:
Thank you everyone for your valuable input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial