Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Specify default value for a date field in SQL Server

Posted on 2014-11-24
13
Medium Priority
?
11,100 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

971 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