AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Friday, 1 July 2016

How to get a list of all weekdays between the given date range in SQL?

In this article I am going to discuss How to get all week days between given date range in SQL? Please check below code to get all weekdays  between given date range

SQL CODE:
DECLARE @MinDate DATE = '20161001',
        @MaxDate DATE = '20161031'

;WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT  Date = DATEADD(DAY, N - 1, @MinDate),
weekday=datename(dw,DATEADD(DAY, N - 1, @MinDate))

FROM    N4
WHERE 
  N < DATEDIFF(DAY, @MinDate, @MaxDate) + 2 AND
  DATEDIFF(DAY, 1 - N, @MinDate) % 7 NOT IN (5,6)

In above example I am getting list of week days between 1-OCT-2016 to 31-OCT-2016.


0 comments :

Post a comment

  • Popular Posts
  • Comments