how to use getdate in user defined function

• Ponnuru venkateswarlu

  SSC-Addicted

  Points: 418

  More actions

  August 13, 2004 at 1:35 am

  #109376

  hi,

  I want current year and month like YYMM in user defined function. How can i achieve this one.

  Thanks in advance.

  rgds,

  venkat

   

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  August 13, 2004 at 2:09 am

  #518812

  Both of these functions are not deterministic. Therefore, they cannot be used inside a UDF. You can work around this by putting them into a view and then referencing the view inside the UDF:

  create view MyView

  as

  select getdate() as TheDate

  go

  create function dbo.MyFunc ()

  returns datetime

  as

  begin

  return (select * from MyView)

  end

  Thanks,

  Ganesh

• Dølle Døck

  Mr or Mrs. 500

  Points: 577

  More actions

  August 13, 2004 at 2:16 am

  #518815

  You could also try this:

  CREATE FUNCTION DBO.fnYearMonth ( @Date DATETIME )

  RETURNS VARCHAR(4)

  AS

  BEGIN

   DECLARE @Verdi VarChar(12)

   Set @Verdi = SUBSTRING(CONVERT(VARCHAR(12),@Date,12),1,4)

   IF @@ERROR > 0

    Set @Verdi = ''

    

   RETURN @Verdi

  END

   

  SELECT DBO.fnYearMonth(GetDAte())

  - Result : 0408

   

  ---

  Regards,

  Anders Dæmroen
  epsilon.no

• Ponnuru venkateswarlu

  SSC-Addicted

  Points: 418

  More actions

  August 13, 2004 at 4:15 am

  #518825

  hi Ganesh,

  Thanks for the reply. Can you please tell me, how to call a user defined function in a trigger.

  rgds,

  venkat

• Peter Tillotson

  Hall of Fame

  Points: 3325

  More actions

  August 16, 2004 at 7:44 am

  #519101

  You could also use:

  replace(convert(char(5), getdate(),11), '/', '')

  which would mean that a UDF isn't needed, unless you really want one. If you are using a UDF you could pass this as a parameter.

  Peter