Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Insert

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2010-05-27 : 23:20:19

Insert into LoginDetails(Username,BranchCode,LoginDate,MachineName,SlNo,MachineIp,RunExeDate)
Values('ASM','BR00001','27/05/2010 8:40:17 PM','SERVER2005',75,'','27 May 2010')

Error:-
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

But some time it is working perfectly in sql 2008 but some time not.

But it is perfectly working without any error if it is sql 2005.

Is any problem in syntax or any option i have to check in sql 2008.

logindate - datatype - datetime

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 01:41:13

It depends on regional settings...

Give the value in ISO 8601 format and you will never have a problem:

declare @LoginDate datetime
--set @LoginDate = '27/05/2010 8:40:17 PM'
set @LoginDate = '2010-05-27T20:40:17'
select @LoginDate

No, you're never too old to Yak'n'Roll if you're too young to die.