Problem in insert a datetime into SqlServer 2005

I user Visual Studio 2005 64 bit ,windowxp 64 bit ,sqlserver 2005

The Sql statement : "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',28/3/2006,4/5/2006,4/5/2006 ) "

and the result in Datebase (All the time become 1/1/1900 0:00:00 )

Although i change the datetype from datetime to smalldatetime the result is same

and i try input the date 28/3/2006 0:00:00 into server but

it show the error:Incorrect syntax near '0'.

What wrong ? help me please,Thank.

They have to be passed as strings:
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test','3/28/2006','5/4/2006','5/4/2006' ) "
or as ISO values which is preferable
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',20060328,20060405,20060405 ) "
HTH, jens Suessmeyer.

|||

Your date is in the future (28th of March this year). That is not allowed:

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 296, Level 16, State 3, Line 5
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

HTH

|||

I prefer to use dates in the following format 'yyyy-mm-dd'.

Have you tried that already (as mentioned above)?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

@.Original Poster: Could you please track the status of the post ? Thanks.

-Jens

sql