TABLE VALUED FUNCTION

 USE [SystemTracker]

GO

/****** Object:  UserDefinedFunction [dbo].[FN_WEEKDATE_START_END_WITH_NAME]    Script Date: 17-11-2021 15:36:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*---------------------------------------------------------------------------------------------------------

FUNCTION NAME       : FN_WEEKDATE_START_END_WITH_NAME

FUNCTION / PURPOSE : FUNCTION WILL RETURN WHOLE WEEK DATE WITH WEEK DAY NAME TABLE FOR PROVIDED DATE

AUTHOR NAME : SATISH SAINI

CREATED DATE : 09-10-2018

REVISION HISTORY 1 :

-----------------------------------------------------------------------------------------------------------*/

/*

SELECT * FROM DBO.FN_WEEKDATE_START_END_WITH_NAME ('2018-10-08')

*/

ALTER FUNCTION [dbo].[FN_WEEKDATE_START_END_WITH_NAME](@P_DATE DATE)

RETURNS @OUTPUTTBL TABLE (WEEKDATE DATE, WEEKDAYNAME VARCHAR(20))

BEGIN

---------------------------------------------------------------------------------------------

-- DEFINED PARAMETER(S)    | VARIABLE DATA TYPE | DEFAULT VAL -- COMMENTS

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

--INSERT INTO @OUTPUTTBL (DATEVALSTART, DATEVALEND)

--VALUES (CONVERT(DATE,DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101')), --GIVES THE DATE OF MONDAY

--CONVERT(DATE,DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101'))); -- GIVES THE DATE OF SUNDAY

 

DECLARE @DATE DATETIME = CAST(@P_DATE AS DATETIME)

INSERT INTO @OUTPUTTBL (WEEKDATE, WEEKDAYNAME)

SELECT WEEKSTART AS WDATE, DATENAME(WEEKDAY,CAST(WEEKSTART AS DATE)) AS WEEKDAYNAME FROM ( SELECT DATEADD(WK, DATEDIFF(WK, 0, @DATE - 1), 0) AS WEEKSTART) DT

UNION

SELECT WEEKSTART + 1 AS WDATE, DATENAME(WEEKDAY,CAST(WEEKSTART + 1 AS DATE)) AS WEEKDAYNAME FROM ( SELECT DATEADD(WK, DATEDIFF(WK, 0, @DATE - 1), 0) AS WEEKSTART) DT

UNION

SELECT WEEKSTART + 2 AS WDATE, DATENAME(WEEKDAY,CAST(WEEKSTART + 2 AS DATE)) AS WEEKDAYNAME FROM ( SELECT DATEADD(WK, DATEDIFF(WK, 0, @DATE - 1), 0) AS WEEKSTART) DT

UNION

SELECT WEEKSTART + 3 AS WDATE, DATENAME(WEEKDAY,CAST(WEEKSTART + 3 AS DATE)) AS WEEKDAYNAME  FROM ( SELECT DATEADD(WK, DATEDIFF(WK, 0, @DATE - 1), 0) AS WEEKSTART) DT

UNION

SELECT WEEKSTART + 4 AS WDATE, DATENAME(WEEKDAY,CAST(WEEKSTART + 4 AS DATE)) AS WEEKDAYNAME  FROM ( SELECT DATEADD(WK, DATEDIFF(WK, 0, @DATE - 1), 0) AS WEEKSTART) DT

UNION

SELECT WEEKSTART + 5 AS WDATE, DATENAME(WEEKDAY,CAST(WEEKSTART + 5 AS DATE)) AS WEEKDAYNAME  FROM ( SELECT DATEADD(WK, DATEDIFF(WK, 0, @DATE - 1), 0) AS WEEKSTART) DT

RETURN 

END