December 8, 2011 at 1:46 am
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!
December 8, 2011 at 3:14 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 8, 2011 at 4:20 am
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.
December 8, 2011 at 4:34 am
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
December 8, 2011 at 2:18 pm
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.
December 8, 2011 at 2:28 pm
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.
December 8, 2011 at 2:38 pm
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.
December 12, 2011 at 1:42 am
Thanks for the help, I changed Datetime to date and managed to get the result that I needed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply