the cast function in transact-sql 2

[OhioSteve]

Hello,

I am writing a function in transact-sql for sql server 2005. I am trying to cast an sql_variant value to datetime. This generates an error. I would appreciate any syntax advice you can give.

Thanks alot!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[holiday]
( @fDate1 sql_variant )
RETURNS tinyint
AS
BEGIN
DECLARE @myInt tinyInt;
declare @fDate2 datetime;
@fDate2= cast(@fDate1 as datetime);
SET @myInt = 0;
if exists(select * from dbo.holidayTable where observanceDate=@fDate2)
begin
set @myInt=1
end
RETURN @myInt
END

 

[ptheriault]

Steve,
There might be a better way but I would do that like this.

BEGIN
    DECLARE @myInt tinyInt;
    declare @fDate2 datetime;
  --  @fDate2= cast(@fDate1 as datetime);
    SET @myInt = 0;
    if exists(select * from dbo.holidayTable where  convert(char(6),observanceDate,112)= CONVERT(CHAR(6),@fDate2,112))
    begin
        set @myInt=1
    end
  RETURN @myInt
END

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Use @fdate1 in the where clause and not @fdate2

- Paul
- Database performance looks fine, it must be the Network!

 

[OhioSteve]

I appreciate your speedy response!

Initially I just made the parameter datetime instead of sql_variant. That worked fine unless one of the values in the recordset wasn't datetime. Then my function exploded. Data is not always clean. If the 1000th row contains "0101/2000" instead of "01/01/2000", I want the function to still run.

Now I plan to have two variables. fdate1 will be sql_variant and it will be the parameter. fdate2 will be datetime. When the function starts, I will try to cast fdate1 into fdate2. If the cast fails, I will return -1. That way the function will not explode, but I will know that a problem occurred.

 

[ptheriault]

you can use ISDATE to check for the existance of a valid date.

- Paul
- Database performance looks fine, it must be the Network!

 

[OhioSteve]

I tried using the isDate test on my sql_variant. I got this error:

Argument data type sql_variant is invalid for argument 1 of isdate function.

 

[gmmastros]

You will need to convert the sql_variant to a varchar data type first.

Select IsDate(Convert(VarChar(20), [!]FieldToTest[/!]))
From ...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[OhioSteve]

Okay, I have changed the parameter's datatype from sql_variant to varchar(100). That STILL keeps the function alive when you input a non-date, but you can do more with it than you can with an sql_variant.

 

[ptheriault]

Steve,
Does your function work with the convert statement now?


- Paul
- Database performance looks fine, it must be the Network!

 

[OhioSteve]

Well, it is MOSTLY working now. Ultimately I want it to return one of these values-

0=not a date
1=date is before or after the years on my holiday table
Note: My holiday table lists each holiday we observed/will observe between 2000 and 2010.
2=not a holiday
3=holiday

Right now my function returns 0,2, and 3 accurately. I have not dealt with 1 yet. I need to grab a value from a select statement to perform that check...I'm not sure how to do that. Anyway, this is my current syntax-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[holiday]
( @fDate varchar(100) )
RETURNS tinyint
AS
BEGIN
DECLARE @myInt tinyInt;
SET @myInt = 0;
if isDate(@fDate)=1
begin
if exists(select * from dbo.holidayTable where observanceDate=@fDate)
begin
set @myInt=3
end
else
begin
set @myInt=2
end
end
RETURN @myInt
END

 

[gmmastros]

Does this do it for ya?

ALTER FUNCTION [dbo].[holiday]
  ( @fDate varchar(100) )
RETURNS tinyint
AS
BEGIN
    DECLARE @myInt tinyInt;    
	DECLARE @MinDate DateTime
    DECLARE @MaxDate DateTime

    SET @myInt = 0;
    if isDate(@fDate)=1
      begin
        if exists(select * from dbo.holidayTable where observanceDate=@fDate) 
          begin
            set @myInt=3
          end 
        else
          begin

            Select @MinDate = Min(observanceDate),
                   @MaxDate = Max(observanceDate)
            From   dbo.holidayTable

            If Convert(DateTime, @fDate) Between @MinDate And @MaxDate
              Begin
                set @myInt=2
              End
            Else
              Begin
                set @MyInt = 1
              End
          end
      end
  RETURN @myInt
END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[OhioSteve]

gmmastros,

You REALLY helped me with getting the min value. Thanks alot! However, you did overlook something. The last holiday of the year will be 12/25. What if the parameter is 12/26, for the LAST year in the table? Your logic would return "out of range" but it should be "not a holiday".

everyone-

Thanks to help from all of you, I have completed the function. You all get stars. Happy Holidays!

PS:
Here is the final product...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[holiday]
( @fDate varchar(100) )
RETURNS tinyint
AS
BEGIN

--If the value is not a date, so return 0 and terminate.
if isDate(@fDate)=0
begin
return 0;
end

--If the date is from a year BEFORE we started
-- keeping records, return 1 and terminate.
DECLARE @minYear Int
Select @minYear = year(Min(observanceDate)) From dbo.holidayTable
if @minYear > year(@fDate)
begin
return 1
end

--For years FAR in the future, we may not have a union contract yet.
--Return 1 and terminate if the date is too far in the future.
DECLARE @maxYear Int
Select @maxYear = year(Max(observanceDate)) From dbo.holidayTable
if @maxYear < year(@fDate)
begin
return 1
end

--If the date is NOT in the holiday table, return 2 and terminate
if not exists(select * from dbo.holidayTable where observanceDate=@fDate)
begin
return 2
end

--The date IS a holiday, so return 3 and terminate.
return 3;
END