i am trying to get number of 2nd and 4th saturday's between 2 dates
Ex:
DECLARE @FromDate date='08/01/2016'
DECLARE @ToDate date='09/20/2016'
Please Help me advance thanks..
I use a UDF to create dynamic date ranges, but a Tally/Date Table would do the trick as well
with cteBase as (
Select *
,RowNr=Row_Number() over (Order by RetVal)
From [dbo].[udf-Create-Range-Date]('2016-08-01','2016-09-20','DD',1)
Where DateName(DW,RetVal)='Saturday'
)
Select RetVal From cteBase Where RowNr in (2,4)
Returns
RetVal
2016-08-13 00:00:00.000
2016-08-27 00:00:00.000
The UDF if needed
CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)
Returns
@ReturnVal Table (RetVal datetime)
As
Begin
With DateTable As (
Select DateFrom = @DateFrom
Union All
Select Case @DatePart
When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
End
From DateTable DF
Where DF.DateFrom < @DateTo
)
Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
Return
End
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)
EDIT to Return Every OTHER Saturday within Range
with cteBase as (
Select *
,RowNr=Row_Number() over (Order by RetVal)
From [dbo].[udf-Create-Range-Date]('2016-09-17','2016-10-29','DD',1)
Where DateName(DW,RetVal)='Saturday'
)
Select RetVal From cteBase Where RowNr % 2 = 0
Returns
RetVal
2016-09-24 00:00:00.000
2016-10-08 00:00:00.000
2016-10-22 00:00:00.000
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments