Datetime Conversion Error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Datetime Conversion Error

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-06-02 : 12:59:34

I am running the following query:

SELECT *
FROM R9.tb_IT0041_176575Dates
WHERE
ISDATE([Date Z6]) <> 0 AND
(([Date Type Z6-Date of Death] <> 'Z8') AND (ISNULL(LTRIM(RTRIM([Date Z6])),'') <> '' AND convert(datetime,[Date Z6]) > getdate()))

It generates the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Clearly I'm not excluding the non-date values from the [Date Z6] column correctly, Can anyone help me get this working?

Basically, we want to make sure that if [Date Z6], which is the Date of Death, is populated, it is not a date in the future for anyone with a value of 'Z8' in the [Date Type Z6-Date of Death] column.

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 14:43:39

Why are you converting [Date Z6] it to datetime?Isn't the datatype of [Date Z6] datetime?

PBUH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-02 : 17:06:15

Try something like this:

where
	case
	when ISDATE([Date Z6]) <> 1 then 0
	when [Date Type Z6-Date of Death] = 'Z8' then 0
	when (ISNULL(LTRIM(RTRIM([Date Z6])),'') = '' then 0
	when convert(datetime,[Date Z6]) <= getdate() then 0
	else 0
	end = 1

CODO ERGO SUM

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-06-03 : 13:44:25

Actually, the datatype of [Date Z6] is varchar. Don't ask, it's not my decision. What I discovered is that there were stray carriage returns in the data. Doing REPLACE([Date Z6], CHAR(13), '') fixed it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources