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?
 
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
 
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
Simple Misconfiguration =Network Vulnerability

In this technical webinar, AlgoSec will present several examples of common misconfigurations; including a basic device change, business application connectivity changes, and data center migrations. Learn best practices to protect your business from attack.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.