DateTime Update Statement

I've looked through books online and can't understand why the below statement doesn't work:

update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'

The error returned is:

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

The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.

Thanks,

BrentOriginally posted by baolive
I've looked through books online and can't understand why the below statement doesn't work:

update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'

The error returned is:

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

The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.

Thanks,

Brent

What's the name of Month 27?|||Cut and paste this:

(Do code tags work here?)

USE Northwind
GO
CREATE TABLE tblregionalmarketrate (effectivedate datetime)
GO
INSERT INTO tblregionalmarketrate (effectivedate)
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate()
GO
SELECT * FROM tblregionalmarketrate
GO

UPDATE tblregionalmarketrate
SET effectivedate = '2003-01-27 00:00:00.000'
WHERE effectivedate > '2003-01-01 00:00:00.000'
GO

SELECT * FROM tblregionalmarketrate
GO

DROP TABLE tblregionalmarketrate
GO|||Thanks, see told you it would be simple, just had the month and day backwards. Could have sworn I tried it with the correct format before.