datetime error handling - 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 2000 Forums

Transact-SQL (2000)

datetime error handling

Author

Topic

dumbo
Starting Member

8 Posts

Posted - 2002-05-08 : 21:55:46

My table has all kinds of garbage in the "SDATE" field. I want to select (with a case statement) which sets garbage values to null, and real date values to real dates....

The table is on a linked server and there appears to be a discrepancy between some of the settings on the two servers....but anyhow....

eg
Table A - SDATE
NULL
<blank> - ie ''
00000000
01010101
20001010
19990305

I've tried to do this:
select mydate = case when sdate like '199%' then convert(datetime, sdate, 113) when sdate like '200%' then convert(datetime, sdate, 113) else null end
from A

but I get

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1043'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'COLLATE'.

Help me pleeeeze

big ears are better than none

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-09 : 01:50:27

try something like

CASE WHEN CAST(CHR_SDATE AS int) < 19950000 THEN NULL ELSE CONVERT(datetime, CHR_SDATE, 113) END

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources