How to get count of 2nd and 4th Saturday's exactly between 2 dates using sql query

Sreevardhan

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..

John Cappelletti

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to get list of 2nd and 4th Saturday dates in SQL Server?

From Dev

How do I get the 2nd and 4th character from a string

From Dev

How to hide the 2nd and 4th table row using css or jquery?

From Dev

How to hide the 2nd and 4th table row using css or jquery?

From Dev

How to handle this: There are five api requests running parallelly and 2nd request is dependent on 4th request's response

From Dev

how function return 2nd and 4th value using collection in oracle? answer would be 42 and 46

From Dev

Suffixes on dates (1st, 2nd, 3rd, 4th etc)

From Dev

How to get the 2nd highest from a table where it need to be added first in sql server in a single query?

From Dev

Django get 2nd,3rd,4th column from row. for loop

From Dev

how to remove 2nd 0r 4th div padding with Pure CSS not jquery

From Dev

How to check whether a date is the 2nd or 4th Sunday of the month in MySQL?

From Dev

How to write 2nd & 4th column to another file

From Dev

How to switch 2nd and 4th digit(before comma) in a double? C programming

From Dev

How to get the 2nd last row inserted using time?

From Dev

How to write query in 1 to many relation, to get random 1 record from 2nd table in sql query

From Dev

Get 2nd occurence of between a pattern

From Dev

Powershell get-date in 1st,2nd,2rd,4th Formats

From Dev

popBackStackImmediate from 4th fragment to the 2nd fragment?

From Dev

popBackStackImmediate from 4th fragment to the 2nd fragment?

From Dev

JavaScript ignores appendChild in 2nd and 4th loop

From Dev

How to select the HTML table's 2nd column to 5th column in the 2nd row?

From Dev

PHP SQL Query between 2 dates

From Dev

How to count fractional days between 2 dates

From Dev

How to count and sum a field between 2 dates?

From Dev

Get List of 1st and 3rd Saturday of month if 2nd Saturday Appear in first 9 days

From Dev

Excel changes dates where the day is lower than the month (e.g 2nd april) to the reverse (e.g 4th feb)

From Dev

How to round a number to 2nd decimal from a SQL query returned to textbox

From Dev

How to round a number to 2nd decimal from a SQL query returned to textbox

From Dev

How to run the 2nd call query in php using ajax with mutiple calls

Related Related

  1. 1

    How to get list of 2nd and 4th Saturday dates in SQL Server?

  2. 2

    How do I get the 2nd and 4th character from a string

  3. 3

    How to hide the 2nd and 4th table row using css or jquery?

  4. 4

    How to hide the 2nd and 4th table row using css or jquery?

  5. 5

    How to handle this: There are five api requests running parallelly and 2nd request is dependent on 4th request's response

  6. 6

    how function return 2nd and 4th value using collection in oracle? answer would be 42 and 46

  7. 7

    Suffixes on dates (1st, 2nd, 3rd, 4th etc)

  8. 8

    How to get the 2nd highest from a table where it need to be added first in sql server in a single query?

  9. 9

    Django get 2nd,3rd,4th column from row. for loop

  10. 10

    how to remove 2nd 0r 4th div padding with Pure CSS not jquery

  11. 11

    How to check whether a date is the 2nd or 4th Sunday of the month in MySQL?

  12. 12

    How to write 2nd & 4th column to another file

  13. 13

    How to switch 2nd and 4th digit(before comma) in a double? C programming

  14. 14

    How to get the 2nd last row inserted using time?

  15. 15

    How to write query in 1 to many relation, to get random 1 record from 2nd table in sql query

  16. 16

    Get 2nd occurence of between a pattern

  17. 17

    Powershell get-date in 1st,2nd,2rd,4th Formats

  18. 18

    popBackStackImmediate from 4th fragment to the 2nd fragment?

  19. 19

    popBackStackImmediate from 4th fragment to the 2nd fragment?

  20. 20

    JavaScript ignores appendChild in 2nd and 4th loop

  21. 21

    How to select the HTML table's 2nd column to 5th column in the 2nd row?

  22. 22

    PHP SQL Query between 2 dates

  23. 23

    How to count fractional days between 2 dates

  24. 24

    How to count and sum a field between 2 dates?

  25. 25

    Get List of 1st and 3rd Saturday of month if 2nd Saturday Appear in first 9 days

  26. 26

    Excel changes dates where the day is lower than the month (e.g 2nd april) to the reverse (e.g 4th feb)

  27. 27

    How to round a number to 2nd decimal from a SQL query returned to textbox

  28. 28

    How to round a number to 2nd decimal from a SQL query returned to textbox

  29. 29

    How to run the 2nd call query in php using ajax with mutiple calls

HotTag

Archive