Default Date SQL Server 2008

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
LVL 11
Guru JiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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 ExpertCommented:
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 ConsultantCommented:
0000-00-00 is an invalid date, it cannot be stored in a date field.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase ExpertCommented:
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 ExpertCommented:
If you have existing column just use below -

ALTER TABLE tstDefault ADD CONSTRAINT DF_DateDeafult DEFAULT '1900/01/01' FOR datecolumn;
ste5anSenior DeveloperCommented:
@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.
Guru JiAuthor Commented:
How can I insert only year and month only when there is no day information available in datetime field
Pawan KumarDatabase ExpertCommented:
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 ConsultantCommented:
>>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.
Guru JiAuthor 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 ExpertCommented:
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 ExpertCommented:
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 HornMicrosoft SQL Server Data DudeCommented:
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 EngineerCommented:
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 ExpertCommented:
Vitor - Its Pawan not Pawam
Vitor MontalvãoMSSQL Senior EngineerCommented:
Apologizes, Pawan.
Pawan KumarDatabase ExpertCommented:
:)
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
pcelbaCommented:
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.
Guru JiAuthor Commented:
Thank you everyone for your valuable input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.