catching convert(,,) error in UDF

Question

Post reply

catching convert(,,) error in UDF

Joseph Hirn

SSC Veteran

Points: 286
More actions
August 19, 2004 at 9:48 am

#108584
Hello. I'm trying to write a function which will convert some 4(integer) column date values and return a datetime type. If the date is null in my source, it is all zeros. Catching this easy but I would like to catch a potentially wrong value as well to make my function more reliable. Is there a way to trap an error on the convert function and have it return the minimum date on failure. My only other alternative is to write a more involved script to check the date values myself, which is possible, but I wanted to know if there is a way to trap the error raised by the Convert(,,) function.
Here is my Stored Procedure. It's pretty straight-forward what I'm trying to accomplish, but the function will bomb out as soon as I use values that would overflow or underflow the convert function.
```
CREATE FUNCTION mdcyToDT(@m int, @d int , @c int ,@y int)
--m(month), d(day), c(Century), y (Year)
```
```
RETURNS datetime
```
```
AS
```
```
BEGIN
 DECLARE @returnDate AS datetime, --return date
   @fullDate As int -- ISO standard Date
```
```
   SET @fullDate = (@c*1000000)+ (@y*10000) + (@m*100) + @d
   SET @returnDate = Convert(datetime,cast(@fullDate as char(8)), 112) --bombs here if bad date
     
   IF @@Error <> 0 
    BEGIN
     SET @returnDate = '01/01/1753'
    END
 RETURN @returnDate
END 
```

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

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

dhilditch SSC-Addicted Points: 495 More actions · Answer 1

ISDATE ( expression )

Use the above before attempting to convert.

Dave Hilditch.

Joseph Hirn SSC Veteran Points: 286 More actions · Answer 2

David.. Thanks that should work for me in this case.

But is there a way to trap the error for perhaps another case? I know Yukon is supposed to have a try catch functionality. That's the effect I'm after.

dhilditch SSC-Addicted Points: 495 More actions · Answer 3

I've not done this for a while but I believe if you stick what you want inside a stored procedure and handle the error from outside the stored procedure then you can simulate try/catch. Don't quote me on this, but if memory serves me right then this should work. May also work if you place the code you need to try inside a function call and place the catch (check @@ERROR) after the call to the function.

Dave Hilditch.

ps. Can you let me know if this works so I can update my memory banks?

A. Novick SSC Veteran Points: 221 More actions · Answer 4

Hi Dave,

Error trapping in UDF is very limited. You're best approach is validating the input before use. In this case, the ISDATE function would do the trick.

There's also the issue of what to do with invalid data? Usually returning NULL is about the best you can do because you can't do RAISERROR in a UDF.

I've written a chapter about error handling in UDFs in my book Transact-SQL User-Defined Functions.

Regards,

Andy

Andrew Novick

http://www.NovickSoftware.com