Specify default value for a date field in SQL Server

What syntax should be used to specify the default value for a date field (not a datetime field) in SQL Server 2014.

None of the ways to specify a default for a datetime or smalldatetime field works, they all end up with a validation error.

Thanks.
LVL 40
Jacques Bourgeois (James Burger)PresidentAsked:
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.

ste5anSenior DeveloperCommented:
What have you tried? The standard works quite well:

CREATE TABLE #test
    (
      DateCol DATE CONSTRAINT DF_Test_DateCol DEFAULT ( GETDATE() )
    );

Open in new window

0
HainKurtSr. System AnalystCommented:
maybe this?

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Spell out the context in which you want a default date.  
If it's in the create table, then...
CREATE TABLE #tmp (id int, dt date DEFAULT CAST(GETDATE() as date)) 

INSERT INTO #tmp (id) values (1), (2), (3)

SELECT * FROM #tmp

Open in new window

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

ste5anSenior DeveloperCommented:
@Jim, there's no need for the CAST().
0
HainKurtSr. System AnalystCommented:
what error do you get?

this is another way of converting datetime or getting date part of datetime

convert(date, getdate())
0
Scott PletcherSenior DBACommented:
Try:
CURRENT_TIMESTAMP --the ansi-standard method
or
SYSDATETIME --another method that avoids using "()"


GETDATE() is actually Sybase syntax carried over to SQL and is not ansi-compliant, so it may be being phased out by MS.
0
HainKurtSr. System AnalystCommented:
GETDATE() is actually Sybase syntax carried over to SQL and is not ansi-compliant, so it may be being phased out by MS.

no way :) zillions of code using getdate()...
0
Jacques Bourgeois (James Burger)PresidentAuthor Commented:
Sorry, my question was incomplete. I need to enter a specific date, not something relative to the current date.

1960-01-01 does not work
(1960-01-01) does not work
((1960)-(01)-(01)) does not work

The error is simply "Error validating the default for column 'ColumnName'".
0
Scott PletcherSenior DBACommented:
'19600101' --'YYYYMMDD' is the only straightforward 100% safe format

Date/datetimes in SQL Server are literal constants, i.e. with quotes, not numeric constants.

Without quotes:
1960-01-01
means 1960 minus 1 minus 1 = 1958
0

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
Jacques Bourgeois (James Burger)PresidentAuthor Commented:
It would never have occured to me that entering a date as text would work, but it does. Somehow there is an automatic conversion in the background.

Thanks Scott.
0
ste5anSenior DeveloperCommented:
As long as TIMESTAMP is an alias for ROWVERSION, I'm not using CURRENT_TIMESTAMP. Cause this is a weird semantic "overload", which may cause more harm then ANSI compliance may solve otherwise.
0
Scott PletcherSenior DBACommented:
?  How's that?  What do "TIMESTAMP" and "CURRENT_TIMESTAMP" have to do with each other?  Just because "TIMESTAMP" was an egregious misnomer is no reason to avoid CURRENT_TIMESTAMP, esp. if you have to port to other RDBMSs.
0
ste5anSenior DeveloperCommented:
Cause I've already worked with guys who made that mistake..
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.