Getdate() in a UDF

• cbarus

  SSCommitted

  Points: 1909

  More actions

  October 17, 2003 at 11:13 am

  #82449

  Hi all-

  I have a situation where I need to get the system datetime from within a UDF. And I get an error. Is there a work around. (Note that I can't pass getdate() as a parameter to this function as I am calling this function from another UDF)

  Thanks for you time

  - Sarat

• rs

  SSCrazy

  Points: 2819

  More actions

  October 17, 2003 at 11:39 am

  #478671

  Hi Sarat,

  Create a view to return getdate() and use that view in your UDF. You cannot call getdate() function in UDF.

  CREATE VIEW get_date

  AS

  SELECT CURRENT_TIMESTAMP AS CUR_DATE

  Thanks.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  October 17, 2003 at 12:28 pm

  #478674

  Hi Sarat,

  a short explaination why you need to wrap it in a view:

  GETDATE() is nondeterministic, meaning same input different output.

  SQL Server can handle only deterministic stuff in a function.

  Check BOL for further reading

  nondeterministic functions -> Deterministic and Nondeterministic Functions

  Frank

  http://www.insidesql.de

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• sholliday

  SSCrazy

  Points: 2100

  More actions

  October 19, 2003 at 9:49 pm

  #478735

  Heres a function I wrote:

  if exists (select * from sysobjects where id = object_id('dbo.fnc_short_date') and xtype = 'FN')

  drop function dbo.fnc_short_date

  GO

  CREATE FUNCTION fnc_short_date (@datInputDate datetime, @separator char(1))

  RETURNS

  varchar(128)

  AS

  BEGIN

  RETURN

  CONVERT(Nvarchar(20), datepart(mm,@datInputDate))

  + @separator

  + CONVERT(Nvarchar(20), datepart(dd, @datInputDate))

  + @separator

  + CONVERT(Nvarchar(20), datepart(yy, @datInputDate))

  END

  GO

  --OPTIONAL--GRANT REFERENCES ON fnc_short_date TO myUSER