Default Date SQL Server 2008

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

OUTPUT

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

(1 row(s) affected)

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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)

If you have existing column just use below -

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

ste5an

@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 Ji

ASKER

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

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)

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Guru Ji

ASKER

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.

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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 Horn

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

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.

Vitor - Its Pawan not Pawam

Apologizes, Pawan.