Getdate() in a function

[timscronin]

I have the function below, can you not use getdate() in a function. It tells me Invalid use of 'getdate' within a function.

alter function [dbo].[fnGetOrderAlertsandMessages](@orderid int )
returns varchar(350)
as
begin
declare @ret varchar(350)
select @ret = coalesce(@ret+', ', '') +
case t.FieldValue
when '1' then oa.comments
else t.longdescr
end
from OrderAlerts oa inner join
Translate t on t.TranslateID = oa.TranslateID and t.MaxEffDtFlag = 1 and t.IsActive = 1
where oa.OrderID = @orderid
and t.FieldName in ('Alerts','Messages')
and oa.created < getdate() + 1
group by t.LongDescr, oa.comments, t.fieldvalue
return @ret
end

 

[SQLDenis]

that will wotk in SQL 2005 but not in 2000 because getdate() is non deterministic

modify your function as follows

alter   function [dbo].[fnGetOrderAlertsandMessages](@orderid int ,@d datetime)
returns varchar(350)
as
begin
    declare        @ret varchar(350)
    select        @ret = coalesce(@ret+', ', '') +  
      case        t.FieldValue
         when    '1' then oa.comments
         else    t.longdescr
      end
    from        OrderAlerts oa inner join
                Translate t on t.TranslateID = oa.TranslateID and t.MaxEffDtFlag = 1 and t.IsActive = 1
    where        oa.OrderID = @orderid
    and            t.FieldName in ('Alerts','Messages')
    and oa.created < @d + 1
    group by    t.LongDescr, oa.comments, t.fieldvalue
    return        @ret
end

select dbo.fnGetOrderAlertsandMessages (1,getdate())

here is a quick example

create   function [dbo].[fnGetOrderAlertsandMessages2](@orderid int ,@d datetime)
returns datetime
as
begin
     declare       @ret datetime
    select  @ret = @d + 1
   
    return        @ret
end


select dbo.fnGetOrderAlertsandMessages2 (1,getdate())

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

Normally, no. I think SQL Server 2005 now allows you to use GetDate() in a function. With SQL 2000, you can 'trick' it by creating a view that returns the value from GetDate(), and then use that within the function.

First, Create the view

Create View View_GetDate
As
Select GetDate() As CurrentDate

Then, modify the function to use the view.

alter   function [dbo].[fnGetOrderAlertsandMessages](@orderid int )
returns varchar(350)
as
begin
    [!]declare @CurrentDate DateTime
    Select  @CurrentDate = (Select * From View_GetDate)   
    [/!]
    declare        @ret varchar(350)
    select        @ret = coalesce(@ret+', ', '') +  
      case        t.FieldValue
         when    '1' then oa.comments
         else    t.longdescr
      end
    from        OrderAlerts oa inner join
                Translate t on t.TranslateID = oa.TranslateID and t.MaxEffDtFlag = 1 and t.IsActive = 1
    where        oa.OrderID = @orderid
    and            t.FieldName in ('Alerts','Messages')
    and oa.created < [!]@CurrentDate[/!] + 1
    group by    t.LongDescr, oa.comments, t.fieldvalue
    return        @ret
end

-George

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

 

[timscronin]

i thought about the view but saw some responses that it would give inconsistent results

 

[gmmastros]

Tim,

I see what you mean. Functions are evaluated for each row, so the GetDate() (which includes a time component) can be different for each call to the function.

I suspect that in this case, it would be safe to use the view method because you are filtering on GetDate() + 1. In fact, I suspect that what you really want is to compare the dates, without regard to the time, which is why you are using GetDate() + 1.

-George

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

 

[pthalacker]

George,

Select  @CurrentDate = (Select * From View_GetDate)

Why can't you use:

Set  @CurrentDate = GetDate()

pamela

 

[SQLDenis]

>>Why can't you use: Set @CurrentDate = GetDate()

because you can't use non dertiministic functions inside a UDF in SQL 2000 in SQL 2005 you can

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[pthalacker]

Denis,

Can you use a non-deterministic function in a procedure?

pamela

 

[SQLDenis]

>>Can you use a non-deterministic function in a procedure?

yes

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

http://msdn2.microsoft.com/en-us/library/ms178091.aspx

http://msdn2.microsoft.com/en-us/library/ms187440.aspx

You cannot use non-deterministic function is a user defined fuction. You can use them in a procedure. In this case, Tim is creating a UDF (user defined function) to concatenate a series of values in to a comma delimited string. In my opinion, this is one of the few acceptable uses for a UDF. UDF's can be problematic because of this non-deterministic issue and also for speed issues because UDF's are evaluated for each record returned by the query. This decidedly non-set based behavior tends to cause performance issues.

I've said it before, and I'll say it again. The only time I will use a function is if:

1. I want to hide complexity.
2. I want to re-use the code.

If both criteria are met, then I will use a UDF. Otherwise, the code will go in to a stored procedure.

-George

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