SELECT PhilFactor.dbo.NthDayOfWeekOfMonth ('2017', 'Jun', 'Fri',3)
  /* third Friday in the month of June 2017 */
  
  CREATE FUNCTION dbo.NthDayOfWeekOfMonth (
  @TheYear CHAR(4), --the year as four characters (e.g. '2014')
  @TheMonth CHAR(3), --in english (Sorry to our EU collegues) e.g. Jun, Jul, Aug
  @TheDayOfWeek CHAR(3)='Sun', -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun
  @Nth INT=1) --1 for the first date, 2 for the second occurence, 3 for the third
  RETURNS DATETIME
  WITH EXECUTE AS CALLER
  AS
  BEGIN
  RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE,'1 '+@TheMonth+' '+@TheYear,113)), 0)+ (7*@Nth)-1
          -(DATEPART (WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE,'1 '+@TheMonth+' '+@TheYear,113)), 0))
          +@@DateFirst+(CHARINDEX(@TheDayOfWeek,'FriThuWedTueMonSunSat')-1)/3)%7
  END        
  go
   
  -- in SQL Server 2012 onwards, you can either use either the EXECUTE command or the SELECT command to
  --execute a function. The main difference is in the way that parameters with defaults are handled but
  --also the schema name is not required in the execute syntax.
  DECLARE @ret DateTime
  EXEC @ret = NthDayOfWeekOfMonth '2017', 'Jun', 'Fri',3
  /* third Friday in the month of June 2017 */
  SELECT @ret AS Third_Friday_In_June_2017
  EXEC @ret = NthDayOfWeekOfMonth '2017', 'Jun'
  /* first Sunday in the month of June 2017 */
  SELECT @ret AS First_Sunday_In_June_2017
  --or you can use the conventional syntax. 
  --Note that you have to give all parameters
  SELECT dbo.NthDayOfWeekOfMonth('2017', 'May', DEFAULT, DEFAULT) AS 'Using default',
         dbo.NthDayOfWeekOfMonth('2017', 'May', 'Sun', 1) AS 'explicit'

Popularity 7/10 Helpfulness 3/10 Language sql

Source: www.red-gate.com

Tags: sql scalar execute serve function

Link to this answer
Share Copy Link

Contributed on Aug 14 2022

code fighter

0 Answers Avg Quality 2/10

how to execute scalar function in sql server3x

Comment

CREATE FUNCTION [schema_name.]function_name (parameter_list)
RETURNS data_type AS
BEGIN
    statements
    RETURN value
END
Code language: SQL (Structured Query Language) (sql)

Popularity 5/10 Helpfulness 1/10 Language sql

Source: www.sqlservertutorial.net

Tags: sql scalar execute function

Link to this answer
Share Copy Link

Contributed on Aug 12 2022

MD FAYSAL Ahmed

0 Answers Avg Quality 2/10

how to execute scalar function in sql server

Contents

More Related Answers

how to execute scalar function in sql server

Closely Related Answers

how to execute scalar function in sql serve

how to execute scalar function in sql server3x

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.