CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS DATETIME
AS
BEGIN
/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/
DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)
SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))
SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)
SET @counter =1
WHILE @start_date <= @end_date
BEGIN
IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )
SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)
END
--SELECT @retrunDate
RETURN @retrunDate
END
No comments:
Post a Comment