I created a function that will return 2 dates that depend on an input parameter.
When the parameter is:
- 1 - return start date of current week and end date of current week
- 2 - return start date of last week and end date of last week
- 3 - return start date of current month and end date of current month
- 4 - return start date of last month and end date of last month
Could you please suggest improvements? And what name would be more appropriate for this function?
CREATE FUNCTION [ufn_GetRangeOfDates]
( @Type int
)
RETURNS @Result TABLE
( StartDtaeStartDate date
, EndDate date
)
AS
BEGIN
declare @Today date = GetDate()
-- current weak
declare @CurrentWeakStart date = dateadd(day, 1-datepart(dw, @Today), CONVERT(date, @Today))
declare @CurrentWeakEnd date = dateadd(day, 7-datepart(dw, @Today), CONVERT(date, @Today))
-- last week
declare @LastWeakStart date = DATEADD(dd, DATEPART(DW,@Today)*-1-6, @Today)
declare @LastWeakEnd date =DATEADD(dd, DATEPART(DW,@Today)*-1, @Today)
--current month
declare @CurrentMonthStart date = DateAdd( day, 1 - Day( @Today ), @Today )
declare @CurrentMonthEnd Date = DateAdd( day, -1, DateAdd( month, 1, @CurrentMonthStart ) )
-- last month
declare @LastMonthStart date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today)-1, 0)
declare @LastMonthEnd date = DATEADD(MONTH, DATEDIFF(MONTH, -1, @Today)-1, -1)
insert into @Result
select CASE @Type
WHEN 1 THEN @CurrentWeakStart
WHEN 2 THEN @LastWeakStart
WHEN 3 THEN @CurrentMonthStart
WHEN 4 THEN @LastMonthStart
ELSE NULL
END
, CASE @Type
WHEN 1 THEN @CurrentWeakEnd
WHEN 2 THEN @LastWeakEnd
WHEN 3 THEN @CurrentMonthEnd
WHEN 4 THEN @LastMonthEnd
ELSE NULL
END
RETURN
END -- End of ufn_GetRangeOfDates
