CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jukira
jukira

Reputation: 13

SQL Server date datatype default and constraints

I am having some difficulty with SQL Server where I am creating a table called GUEST 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 GUEST 
 VALUES('John' ,'');

Results in an error:

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

I have done some code to show 

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

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

Upvotes: 1

Views: 1460

Answers (5)

Vishu
Vishu

Reputation: 1

try this insert statement. INSERT INTO #EMPLOYEE (name) VALUES('John' );

Upvotes: 0

Vincent
Vincent

Reputation: 882

SELECT CAST('' as DATE) returns 1900-01-01;

So what your insert statement is trying to insert implicitly is 1900-01-01 since you passed the value ''. If you want the insert to use the default value you have to exclude it completely.

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

Would do the trick

Upvotes: 1

serverSentinel
serverSentinel

Reputation: 994

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.

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

If you wanted to try the default value,no need of passing value to the date field. ' ' is a valid string character and it becomes '1900-01-01' while converting to date type.Also mention the column names in the Insert query while making the insertion like below. If you not mentioning the column names in the INSERT statement,SQL server will try to insert data in the default order of columns .

  INSERT INTO Employee (name, ..OtherColumns) --mention the name of other columns if required 
   VALUES ('John',OtherColumnsValues)

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5432

You should use this

INSERT INTO EMPLOYEE_test(name) VALUES('John');

Your query equal to INSERT INTO EMPLOYEE VALUES('John' , CAST('' as datetime))

When you use the query INSERT INTO EMPLOYEE VALUES('John' ,''); SQLServer will try convert '' to datatime, and that is 1900-01-01 so you get that error.

Upvotes: 0

Related Questions