?
Solved

Specify default value for a date field in SQL Server

Posted on 2014-11-24
13
Medium Priority
?
11,790 Views
Last Modified: 2014-11-25
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.
0
Comment
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 40462654
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 40462655
maybe this?

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40462657
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
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

 
LVL 36

Expert Comment

by:ste5an
ID: 40462661
@Jim, there's no need for the CAST().
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 40462663
what error do you get?

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

convert(date, getdate())
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40462670
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 40462691
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
 
LVL 40
ID: 40462704
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40462712
'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
 
LVL 40

Author Closing Comment

by:Jacques Bourgeois (James Burger)
ID: 40462737
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40463983
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40464621
?  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
 
LVL 36

Expert Comment

by:ste5an
ID: 40464842
Cause I've already worked with guys who made that mistake..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question