jukira jukira - 1 year ago 44
SQL Question

SQL Server date datatype default and constraints

I am having some difficulty with SQL Server where I am creating a table called

EMPLOYEE
and using
date
as a data type, not null and has a default value of 'today' and a constraint of the date must be equal or later than 01 dec 2010.

However, I am encountering a problem when I tried to test by insert a row with default values.

How can I let SQL Server to show the current date instead of the default
getDate()
which defaults to 1900-01-01 which conflicts with my constraint condition of date must be later than 01 -dec-2010

INSERT INTO EMPLOYEE
VALUES('John' ,'');


Results in an error:


The INSERT statement conflicted with the CHECK constraint "chk_DATEJOINED". The conflict occurred table "dbo.EMPLOYEE", column 'dateJoined'.


I have done some code to show

CREATE TABLE EMPLOYEE
(
[...]
employeeNum int NOT NULL IDENTITY(1,1),
name varchar(40) NOT NULL,
dateJoined date NOT NULL DEFAULT GETDATE()
[...]

CONSTRAINT chk_DATEJOINED
CHECK (dateJoined >= CAST('01-dec-2010' as DATETIME))
)

Answer Source

This can be done in several ways.

1) like everyone else has pointed out. State which columns you want to populate and skipping the dateJoined column.

insert into Employee ( name ) values ( 'John' )

2) Use the default keyword as the value.

insert into Employee ( name, dateJoined ) values ( 'John', default )

The default keyword is useful if you're building an SQL statement in code and want the column list explicitly set. Default keyword defaults to null if there isn't a default constraint on the column so watch out for columns declared as not null.