Problem using IsDate function within Case statement in an insert clause

Question

Problem using IsDate function within Case statement in an insert clause

bigmikeg

SSC Veteran

Points: 231
More actions
October 16, 2009 at 3:06 pm

#96347

I'm importing data from a client. Most of the process is straightforward, but I do need to do some evaluation on one column in particular. The source column, named "ACTION" is an nvarchar, and possible values are various dates, 'Y', 'YES', 'N', 'NO', NULL, plus a handful of others.
The destination column in my system is a datetime. For the select part of my clause I'm using:
case ACTION
when isdate(ACTION) then ACTION
when 'Y' then getdate()
when 'YES' then getdate()
else '1/1/1900'
end
The value for ACTION in the first row of the import table is 'NO'. When I try to run my insert statement I get the following error:
Conversion failed when converting the nvarchar value 'NO' to data type int.
It's as if the statement is trying to insert the result of the valuation itself. I'm not new to SQL or case statements, but I can't seem to find a solution to do this evaluation. I've searched the net and seen other articles commenting on the usefulness of IsDate(), and at this point I have to wonder.
Any ideas?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

I know you said you're not new so don't take this the wrong way, but it happens to the best of us so I have to doublecheck:

Have you tried ditching the whole case statement and just using a GETDATE()? Does it still fail?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 2

And to follow that up, if it does not fail, try this case instead:

CASE WHEN ISDATE(Action) = 1 THEN Action

WHEN Action IN ('Y','YES') THEN GETDATE()

ELSE '1/1/1900'

END

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

bigmikeg (10/16/2009)
...
The destination column in my system is a datetime. For the select part of my clause I'm using:
case ACTION
when isdate(ACTION) then ACTION
when 'Y' then getdate()
when 'YES' then getdate()
else '1/1/1900'
end
....

You are using the wrong syntax for the case statement. If is failing because isdate returns an int, and it tries to cast the value in ACTION, 'NO' to an INT to compare it to the result of isdate.

Use the format suggested by Garadin:

case

when isdate(ACTION) = 1

then ACTION

when ACTION in ('Y','YES')

then getdate()

else '19000101'

end