hi,
I have a function that gets a time from an input parameter as nvarchar.
in the function i want to check the input parameter for its validity.
i want to return a specific return value if the time parameter is not valid.
thanks for your help.

God Bless you,
Hossein

__________________
Be Sure,
-hossrad

How about posting your code here?

_________________________
- Vijay G
Strive for Perfection

ALTER FUNCTION dbo.SubtractTime_fx
(
@FromDate datetime,
@FromTime varchar(8),
@ToDate datetime,
@ToTime varchar(8)
)
RETURNS nvarchar(10)
AS
BEGIN
    DECLARE @SecondsBetween int
    DECLARE @HourPart tinyint
    DECLARE @MinPart tinyint
    DECLARE @SecondPart tinyint
    DECLARE @Temp varchar(100)

    SELECT @Temp=CAST(@FromTime as DateTime)
    if @@error<>0
        return '-1'
    SELECT @Temp=CAST(@ToTime as DateTime)
    if @@error<>0
        return '-1'
    if ((@FromDate=@ToDate) and (@FromTime>@ToTime)) or (@FromDate>@ToDate)
        return '-1'
    SELECT @SecondsBetween=DateDiff(second,+Cast(IsNull(@From Date,0) as datetime)+Cast(@FromTime as datetime),CAST(IsNull(@FromDate,0) as datetime)+Cast(@ToTime as dateTime))
    --return CAST(@SecondsBetween as nvarchar)
    SET @HourPart=@SecondsBetween / 3600
    SET @SecondsBetween= @SecondsBetween % 3600
    SET @MinPart=@SecondsBetween /60
    SET @SecondsBetween= @SecondsBetween % 60
    SET @SecondPart=@SecondsBetween
    return CAST(@HourPart as nvarchar)+':'+SUBSTRING('00',1,2-Len(CAST(@MinPart as nvarchar)))+CAST(@MinPart as nvarchar)+':'+SUBSTRING('00',1,2-Len(CAST(@SecondPart as nvarchar)))+CAST(@SecondPart as nvarchar)
END



God Bless you,
Hossein

Ideally you should be doing the validation in your function and return something like True or False to say if the validation succeeded or failed. based on that you should be proceeding further.

_________________________
- Vijay G
Strive for Perfection

how should i do the validation?
i tried to cast it as datetime so that if the cast returned an error i can say its not a valid datetime! but when i call the function if the cast returns error the function breaks and returns the error! i want to handle the casting error in my function so that the function call doesnt return the error

God Bless you,
Hossein

The question here is why is the time passed in as a different parameter when sql server supports the Time part in the DATETIME Type? And how is the time being passed? What if someone passed alphanumeric values to time variables? obviously the CAST will fail.

_________________________
- Vijay G
Strive for Perfection

Thats exactly the point! i just want to handle the cast fail myself.
i dont want the cast to work. i want when it fails the whole function does not fail! something like writing this code in delphi :
try
  a:=StrToDateTime(FromTime);
except
  //FromTime is not a valid time
end;



God Bless you,
Hossein

I dont think you can handle the CAST failure yourself. It is the error in data format that is passed and being subjected to CAST. So it would terminate the procedure/function from processing further. You dont have control of it and resume next in case of such errors, unless you parse the complete time part and confirm that hour:min:second is passed within their limits. These are too much to do manually. Thats why I asked why is that you pass the Time part in a separate parameter and add more to your code to validate it, rather than using the time part of DATETIME datatype, which means when a value passed with a datetime variable, it means that it is a valid entry and avoid manual validations thus.

Hope that explains.
Cheers

_________________________
- Vijay G
Strive for Perfection

Thanks A Lot for your help,
i think i got it!


God Bless you,
Hossein