Case Statement and ISNULL problem

Question

Post reply

Case Statement and ISNULL problem

mic.con87

SSCommitted

Points: 1947
More actions
December 8, 2011 at 1:46 am

#264429

Hi, I'm having an issue with a case statement:
Select Case When ISNULL(DateOfLicense1,convert (datetime, '01-DEC-2015') <= ISNULL(DateOfLicense2,convert (datetime, '01-DEC-2015') And ISNULL(DateOfLicense1,convert (datetime, '01-DEC-2015') <= ISNULL(DateOfLicense3,convert (datetime, '01-DEC-2015') Then ISNULL(DateOfLicense1,convert (datetime, '01-DEC-2015')
When ISNULL(DateOfLicense2,convert (datetime, '01-DEC-2015') <= ISNULL(DateOfLicense1,convert (datetime, '01-DEC-2015') And ISNULL(DateOfLicense2,convert (datetime, '01-DEC-2015') <= ISNULL(DateOfLicense3,convert (datetime, '01-DEC-2015') Then ISNULL(DateOfLicense2,convert (datetime, '01-DEC-2015')
When ISNULL(DateOfLicense3,convert (datetime, '01-DEC-2015') <= ISNULL(DateOfLicense1,convert (datetime, '01-DEC-2015') And ISNULL(DateOfLicense3,convert (datetime, '01-DEC-2015') <= ISNULL(DateOfLicense2,convert (datetime, '01-DEC-2015') Then ISNULL(DateOfLicense3,convert (datetime, '01-DEC-2015')
Else convert (datetime, '01-DEC-2014')
End
From Staging.Names
Any help would be appreciated!!! Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You haven't given the description of your problem

Also post the DDL along with some sample data and the expected result

This will help people to give you tested solutions for your problem

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

mic.con87 SSCommitted Points: 1947 More actions · Answer 2

Hi sorry I should have been more explicit. I actually forgot to add ) to the ISNULL conditon. I now have a new issue. The query begins to run and return results but then stops and I receive the following error message:

Msg 242, Level 16, State 3, Line 1

The conversion of a date data type to a datetime data type resulted in an out-of-range value.

For your info, DateOfLicense1,DateOfLicense2,DateOfLicense3 can either all contain dates, no dates at all in which case they are null or there can be a combination of nulls and dates.

Query that runs is as follows:

Select Case When ISNULL(DateOfLicense1,cast('01-DEC-2015' AS datetime)) <= ISNULL(DateOfLicense2,cast('01-DEC-2015' AS datetime)) And ISNULL(DateOfLicense1,cast('01-DEC-2015' AS datetime)) <= ISNULL(DateOfLicense3,cast('01-DEC-2015' AS datetime)) Then ISNULL(DateOfLicense1,cast('01-DEC-2015' AS datetime))

When ISNULL(DateOfLicense2,cast('01-DEC-2015' AS datetime)) <= ISNULL(DateOfLicense1,cast('01-DEC-2015' AS datetime)) And ISNULL(DateOfLicense2,cast('01-DEC-2015' AS datetime)) <= ISNULL(DateOfLicense3,cast('01-DEC-2015' AS datetime)) Then ISNULL(DateOfLicense2,cast('01-DEC-2015' AS datetime))

When ISNULL(DateOfLicense3,cast('01-DEC-2015' AS datetime)) <= ISNULL(DateOfLicense1,cast('01-DEC-2015' AS datetime)) And ISNULL(DateOfLicense3,cast('01-DEC-2015' AS datetime)) <= ISNULL(DateOfLicense2,cast('01-DEC-2015' AS datetime)) Then ISNULL(DateOfLicense3,cast('01-DEC-2015' AS datetime))

Else cast('01-DEC-2014' AS datetime)

End

From Staging.Names

I did try using convert (datetime, '01-DEC-2015') instead of cast('01-DEC-2015' AS datetime) but the same error is returned.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

please post the create table definition of staging.names and also sample data in the form of insert statments

please see the first link in my signature if you have any problems

toddasd SSCertifiable Points: 6298 More actions · Answer 4

Since it's an error converting from date to datetime, have you tried casting your columns as date instead of datetime?

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

toddasd SSCertifiable Points: 6298 More actions · Answer 5

Also, if your columns are char based, then you may have an invalid date. Run this to find it:

SELECT *

FROM dbo.Names

WHERE ISDATE(ISNULL(DateOfLicense1,CAST('01-DEC-2015' AS DATETIME)))=0

OR ISDATE(ISNULL(DateOfLicense2,CAST('01-DEC-2015' AS DATETIME)))=0

OR ISDATE(ISNULL(DateOfLicense3,CAST('01-DEC-2015' AS DATETIME)))=0;

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

toddasd SSCertifiable Points: 6298 More actions · Answer 6

Nevermind that one, think I got it. Check that any of the dates in the table are before 1753:

SELECT *

FROM dbo.Names

WHERE DateOfLicense1 < '01-Jan-1753'

OR DateOfLicense2 < '01-Jan-1753'

OR DateOfLicense3 < '01-Jan-1753';

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

mic.con87 SSCommitted Points: 1947 More actions · Answer 7

Thanks for the help, I changed Datetime to date and managed to get the result that I needed.