Conversion failure with stored procedure?

Question

Conversion failure with stored procedure?

cpicard

Mr or Mrs. 500

Points: 563
More actions
November 23, 2009 at 5:58 pm

#135418

Hi There,
I am having some issues with my cursor/stored procedure/function. I run a cursor to take each "entity_sk" ID from the
selected table, pass each ID into a stored procedure, which in turn runs functions to return specific dates regarding the
ID. These dates are then stored into a Temporary table.
The error I am getting is :
Msg 241, Level 16, State 1, Procedure RPT_TargetDatesReport, Line 412
Conversion failed when converting date and/or time from character string.
The function that is being referenced in "Line 412" is :
ALTER FUNCTION [dbo].[_RPT_udf_GetTop1TodoTargetDate]
(@entity_sk int
,@lawtype_code varchar(2)
,@todo_type_sk int)
RETURNS datetime
BEGIN
Declare @TargetDateTime datetime
set @TargetDateTime = (SELECT top 1 t.deadline_date
FROM vcase_parties def
INNER JOIN Cases c on def.case_sk = c.case_sk
INNER JOIN to_dos t on c.case_sk = t.case_sk
INNER JOIN group_items g on t.todo_type_sk = g.item_sk
where def.entity_sk = @entity_sk
and t.todo_type_sk = @todo_type_sk
and c.lawtype_code = @lawtype_code and c.closed_ind = 'o'
order by deadline_date desc
)
RETURN @TargetDateTime
END
The cursor is as below:
Declare
@entity_sk int
DECLARE myCursor CURSOR FOR (
select
entity_sk
From case_parties
OPEN myCursor
FETCH NEXT FROM myCursor INTO @entity_sk
WHILE @@FETCH_STATUS = 0
BEGIN
Insert into #Table2
exec RPT_TargetDatesReport @entity_sk
FETCH NEXT FROM myCursor INTO @entity_sk
END
CLOSE myCursor
DEALLOCATE myCursor
Select *
From #Table2
any ideas??

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

It's not the code... it's the data. You need to find out which VARCHAR data isn't valid in that column. Start with using ISDATE and go from there.

I won't comment much about losing the cursor. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (11/23/2009)
It's not the code... it's the data. You need to find out which VARCHAR data isn't valid in that column. Start with using ISDATE and go from there.
I won't comment much about losing the cursor. 😉

There's that holiday spirit!

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]

cpicard Mr or Mrs. 500 Points: 563 More actions · Answer 3

Thanks. I'll try that out.

(The cursor was most definitely a necessary evil. :-P)

cpicard Mr or Mrs. 500 Points: 563 More actions · Answer 4

Thanks for all the responses. I found that the dates that were giving me issues were brought over from an old application database. The docketing people entered text into the date fields, and the old application had no way of checking that.

I am now converting. Thanks again.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

cpicard (11/24/2009)
Thanks. I'll try that out.
(The cursor was most definitely a necessary evil. :-P)

Heh... I'm trying to be nice... stop talking about the "C" word. Just let it go... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

cpicard (11/24/2009)
Thanks for all the responses. I found that the dates that were giving me issues were brought over from an old application database. The docketing people entered text into the date fields, and the old application had no way of checking that.
I am now converting. Thanks again.

That's cool... thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)