TRY / EXCEPT on open CURSOR

Question

TRY / EXCEPT on open CURSOR

dankwart menor

Ten Centuries

Points: 1130
More actions
November 23, 2010 at 7:23 pm

#229553

Hi there,
I have a CURSOR that does a CAST from CHAR to SMALLDATETIME in the WHERE clause:
WHERE Date < CAST (SUBSTRING (date_, 1, 4) + '-' +
SUBSTRING (date_, 5, 2) + '-' +
SUBSTRING (date_, 7, 2) AS DATETIME)
Within the processed data there are records like 2010-55-43 which are not valid and cannot be casted.
So when I open the cursor, I get an exception:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How can I TRY/EXCEPT these occurrences? I don't want the whole processing to be cancelled, just the one record should be omitted.
When I TRY/EXCEPT the OPEN cursor, it will cancel the whole operation...
BEGIN TRY
OPEN Data_Cursor;
END TRY
I'd highly appreciate any suggestions!
Cheers
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

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

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

WayneS SSC Guru Points: 95405 More actions · Answer 1

I strongly believe that >99% of all c.u.r.s.o.r.s can be eliminated; therefore I don't help with them. If you will post the table definitions, sample data, and what your expected results are (based on the sample data), I'll be glad to show you a set-based method for doing what you're doing. Please see the first link in my signature for how to post the requested data.

In a set-based method, I would use a CTE with the IsDateTime function to only get the rows that can be converted to a valid date, and then work only on the resulting rows with the convert function. A set-based method would probably run 1000 times faster (or more) than your c.u.r.s.o.r does.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

dankwart menor Ten Centuries Points: 1130 More actions · Answer 2

Thanks mate,

It's just that I don't want to spend a lot of time on refactoring that legacy script. Performance is not an issue, neither is reusing it. The most simple approach would do it.

Cheers

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

davidandrews13 SSCertifiable Points: 6689 More actions · Answer 3

can you use a case statement to filter out the records that can't be converted to date?

something like this? I haven't checked for feburary because i'm not sure how to check whether it would be a leap year or not, yet.

declare @char1 as char(2)

declare @char2 as char(2)

declare @char3 as char(4)

set @char1 = '04'

set @char2 = '30'

set @char3 = '2010'

select * from [YourTable]

where

1 = case when @char1 in (01,03,05,07,08,10,12) and @char2 between 01 and 31 then

case when getdate() > CAST(@char1 + '-' + @char2 + '-' + @char3 as datetime) then 1 end

else case when @char1 in (04,06,09,11) and @char2 between 01 and 30 then

case when getdate() > CAST(@char1 + '-' + @char2 + '-' + @char3 as datetime) then 1 end

else 0 endend