wrong data type?

[jamesd0142]

sql server 2000

declare @a varchar(20)
Set @a = (select CONVERT(CHAR(10),GETDATE(),103))
update Users
SET [Password] = DD3_Password, PasswordChanged = @a
from temptable2
where Users.Login = temptable2.Login
 

I get the error:
'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.'

The data type of PasswordChanged field in Users is Smalldatetime(4)?

can any1 help?

[Jim Doherty]

sql server 2000

declare @a varchar(20)
Set @a = (select CONVERT(CHAR(10),GETDATE(),103))
update Users
SET [Password] = DD3_Password, PasswordChanged = @a
from temptable2
where Users.Login = temptable2.Login
 

I get the error:
'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.'

The data type of PasswordChanged field in Users is Smalldatetime(4)?

can any1 help?

you are converting to Standard British/French dd/mm/yyyy and trying to insert that which actually gets stored as 2007-10-27 00:00:00

You need to swap the month and year round so use the 101 USA nstead of 103 within the convert function

Regards

Jim :)