Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag for Canada asked on

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.
Microsoft SQL ServerDB Dev ToolsMicrosoft Development

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
ste5an

What have you tried? The standard works quite well:

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

Open in new window

HainKurt

maybe this?

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Jim Horn

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
ste5an

@Jim, there's no need for the CAST().
HainKurt

what error do you get?

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

convert(date, getdate())
Scott Pletcher

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

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()...
Jacques Bourgeois (James Burger)

ASKER
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'".
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jacques Bourgeois (James Burger)

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ste5an

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

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

Cause I've already worked with guys who made that mistake..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.