February 17, 2012 at 1:27 pm
First of all... TGIF
I'm getting the following error
"Conversion failed when converting date and/or time from character string"
and it has to do with the following being in the select statment:
CASE WHEN (SELECT MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID) IS NOT NULL
THEN
(SELECT MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID)
ELSE 'To Be Mailed' END AS 'Sent Date',
Any ideas?
Thanks.
Jeremy
February 17, 2012 at 1:47 pm
Run these following statements, you will know!!
select case when 1 = 1 then GETDATE()
when 2 = 0 then 'Paapapapap'
else GETDATE()
end
select case when 1 = 0 then GETDATE()
when 2 = 0 then 'Paapapapap'
else GETDATE()
end
select case when 1 = 0 then GETDATE()
when 2 = 0 then GETDATE()
else 'Paapapapap'
end
select case when 1 = 0 then GETDATE()
when 2 = 0 then 'Paapapapap'
else GETDATE()
end
February 17, 2012 at 1:53 pm
And read about data-type precedence here : MSDN
The values retruned by each expression in CASE will be matched will be converted to the highest data-type in the precedence list!
Thus in your code, per the logic the first "THEN" will return a date! BUt none of your rows in the tabled matched your criteria, thus making the CASE fall to "ELSE" clause! in Else, you have a varchar! But per the data-type precedence, DATE is higher than VARCHAR, thus SQL tries to convert the output from ELSE to a date which were the failure is!!
To overcome it, CAST your "THEN" to VARCHAR.
February 17, 2012 at 1:53 pm
What is the definition for dbo.GetLocalDateTimeFunc?
February 17, 2012 at 1:57 pm
so your new code will be (changes in bold)
CASE WHEN (
SELECT MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID
) IS NOT NULL
THEN
(
-- My changes below, wrapping MIN() with CAST( MIN() As VARCHAR(15))
SELECT CAST ( MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate)) AS VARCHAR(16)
FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID
)
ELSE 'To Be Mailed'
END AS 'Sent Date',
February 17, 2012 at 5:18 pm
Thank you! That appears to have worked. However, now the dates in that column are in a different format:
"2012-01-01 10:12:03.090" vs "Jan 13 2012 2:20AM".
Is there anyway for this to work while keeping dates looking the same? I am going to be putting this into the BI studio, so if that has the ability to display everything consistently, that would work to. Thanks for the help!
February 17, 2012 at 5:27 pm
Jeremy... (2/17/2012)
"2012-01-01 10:12:03.090" vs "Jan 13 2012 2:20AM".Is there anyway for this to work while keeping dates looking the same?
This?
CASE WHEN (
SELECT MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID
) IS NOT NULL
THEN
(
-- My changes below, wrapping MIN() with CAST( MIN() As VARCHAR(15))
SELECT convert (varchar(30),MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate)) ,21)FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID
)
ELSE 'To Be Mailed'
February 20, 2012 at 7:04 am
ColdCoffee (2/17/2012)
Jeremy... (2/17/2012)
"2012-01-01 10:12:03.090" vs "Jan 13 2012 2:20AM".Is there anyway for this to work while keeping dates looking the same?
This?
CASE WHEN (SELECT MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID
) IS NOT NULL
THEN
(
-- My changes below, wrapping MIN() with CAST( MIN() As VARCHAR(15))
SELECT convert (varchar(30),MIN(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate)) ,21)FROM FileActions FA2
WHERE FA2.ActionDefID IN (66,64,391) --Docs Sent To Record Actions
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID
)
ELSE 'To Be Mailed'
That did it. Its perfect now. Thanks everyone!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply