jukira jukira - 2 months ago 9
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

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.

Comments