Solved

Specify default value for a date field in SQL Server

Posted on 2014-11-24
13
8,492 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 34

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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40462655
maybe this?

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

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 34

Expert Comment

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

Expert Comment

by:Huseyin KAHRAMAN
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 69

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 53

Expert Comment

by:Huseyin KAHRAMAN
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 34

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 69

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 34

Expert Comment

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

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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