All Monday date of each month.

Question

Post reply

All Monday date of each month.

farooq.hbs

SSCrazy

Points: 2083
More actions
January 23, 2015 at 5:25 am

#314727

Hi,
How to retrieve only Monday dates from each month in sql server.
If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
Is that possible?
I want to calculate bi weekly range data in the sql.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Christopher Kutsch SSC Eights! Points: 893 More actions · Answer 1

Try this: http://www.sqlservercentral.com/scripts/calendar/120574/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

The easiest solution would be if you had a calendar table.

If not, you can use the following query:

DECLARE @StartDate DATE = '2000-01-01';

DECLARE @EndDate DATE= '2050-12-31';

DECLARE @parDate DATE= GETDATE();

WITH T0 AS (SELECT N FROM (VALUES (1),(1)) AS tmp(N))

,T1 AS (SELECT N = 1 FROM T0 AS a CROSS JOIN T0 AS b)

,T2 AS (SELECT N = 1 FROM T1 AS a CROSS JOIN T1 AS b)

,T3 AS (SELECT N = 1 FROM T2 AS a CROSS JOIN T2 AS b)

,T4 AS (SELECT N = 1 FROM T3 AS a CROSS JOIN T3 AS b)

,T5 AS (SELECT N = 1 FROM T4 AS a CROSS JOIN T4 AS b) -- over 4 billion rows

,Tally AS (SELECT TOP 500000 [Date] = DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate) FROM T5)

SELECT [Date]

FROM Tally

WHERE [Date] <= @EndDate

AND DATENAME(dw,[Date]) = 'Monday'

AND YEAR([Date])= YEAR(@parDate)

AND MONTH([Date])= MONTH(@parDate);

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Something like this would work. . .

DECLARE @PassedInDate AS DATE;

SET @PassedInDate = GETDATE();

WITH CTE ( N )

AS (

SELECT 1

FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) a ( N )

),

CTE2 ( N )

AS (

SELECT 0 -- 0 based

UNION ALL

SELECT TOP 30 -- limit it up front, never more than 31 days in a month!

ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) )

FROM CTE a

CROSS JOIN CTE b

),

CTE3 ( N )

AS (

SELECT DATEADD(DAY, N, DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate), 0))

FROM CTE2

WHERE DATEADD(DAY, N, DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate), 0)) <

DATEADD(MONTH, DATEDIFF(MONTH, 1, @PassedInDate) + 1, 0)

)

SELECT N AS [DATE]

FROM CTE3

WHERE DATENAME(WEEKDAY, N) = 'Monday';

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT MondaysThisMonth = DATEADD(DAY,n,MondayBeforeFOM)

FROM (

SELECT FirstOfMonth, MondayBeforeFOM = DATEADD(DAY,DATEDIFF(DAY,0,FirstOfMonth)/7*7,0)

FROM (SELECT FirstOfMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) d

) e

CROSS JOIN (SELECT 7 UNION ALL SELECT 14 UNION ALL SELECT 21 UNION ALL SELECT 28 UNION ALL SELECT 35) f (n)

WHERE DATEADD(DAY,n,MondayBeforeFOM) < DATEADD(MONTH,1,FirstOfMonth)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

ChrisM@Work (1/23/2015)
SELECT MondaysThisMonth = DATEADD(DAY,n,MondayBeforeFOM) FROM ( SELECT FirstOfMonth, MondayBeforeFOM = DATEADD(DAY,DATEDIFF(DAY,0,FirstOfMonth)/7*7,0) FROM (SELECT FirstOfMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) d ) e CROSS JOIN (SELECT 7 UNION ALL SELECT 14 UNION ALL SELECT 21 UNION ALL SELECT 28 UNION ALL SELECT 35) f (n)
WHERE DATEADD(DAY,n,MondayBeforeFOM) < DATEADD(MONTH,1,FirstOfMonth)

That is wonderful, going straight in my back pocket!

Slightly less verbose if you use the values constructor.

DECLARE @PassedInDate AS DATE;

SET @PassedInDate = '2014-08-01';

SELECT MondaysThisMonth = DATEADD(DAY, n, MondayBeforeFOM)

FROM (

SELECT FirstOfMonth, DATEADD(DAY, DATEDIFF(DAY, 0, FirstOfMonth) / 7 * 7, 0)

FROM ( VALUES ( DATEADD(MONTH, DATEDIFF(MONTH, 0, @PassedInDate), 0)) ) d ( FirstOfMonth )

) e ( FirstOfMonth, MondayBeforeFOM )

CROSS JOIN ( VALUES ( 7), ( 14), ( 21), ( 28), ( 35) ) f ( n )

WHERE DATEADD(DAY, n, MondayBeforeFOM) < DATEADD(MONTH, 1, FirstOfMonth);

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Thanks Cadavre 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Cadavre (1/23/2015)
ChrisM@Work (1/23/2015)
SELECT MondaysThisMonth = DATEADD(DAY,n,MondayBeforeFOM) FROM ( SELECT FirstOfMonth, MondayBeforeFOM = DATEADD(DAY,DATEDIFF(DAY,0,FirstOfMonth)/7*7,0) FROM (SELECT FirstOfMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) d ) e CROSS JOIN (SELECT 7 UNION ALL SELECT 14 UNION ALL SELECT 21 UNION ALL SELECT 28 UNION ALL SELECT 35) f (n)
WHERE DATEADD(DAY,n,MondayBeforeFOM) < DATEADD(MONTH,1,FirstOfMonth)
That is wonderful, going straight in my back pocket!
Slightly less verbose if you use the values constructor.
DECLARE @PassedInDate AS DATE;
SET @PassedInDate = '2014-08-01';
SELECT MondaysThisMonth = DATEADD(DAY, n, MondayBeforeFOM)
FROM (
SELECT FirstOfMonth, DATEADD(DAY, DATEDIFF(DAY, 0, FirstOfMonth) / 7 * 7, 0)
FROM ( VALUES ( DATEADD(MONTH, DATEDIFF(MONTH, 0, @PassedInDate), 0)) ) d ( FirstOfMonth )
) e ( FirstOfMonth, MondayBeforeFOM )
CROSS JOIN ( VALUES ( 7), ( 14), ( 21), ( 28), ( 35) ) f ( n )
WHERE DATEADD(DAY, n, MondayBeforeFOM) < DATEADD(MONTH, 1, FirstOfMonth);

I love it. Nicely done, guys. Thought I'd throw my tweaks into the ring.

DECLARE @PassedInDate DATETIME;

SELECT @PassedInDate = '15 OCT 2014'

;

SELECT Wk# = ROW_NUMBER() OVER (ORDER BY wk.MondayDate), wk.MondayDate

FROM (SELECT DATEADD(dd,DATEDIFF(dd,days.Offset,DATEADD(mm,DATEDIFF(mm,0,@PassedInDate),0))/7*7,0)

FROM (VALUES (-6),(-13),(-20),(-27),(-34))days(Offset)

)wk(MondayDate)

WHERE DATEDIFF(mm,0,wk.MondayDate) = DATEDIFF(mm,0,@PassedInDate)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

Calendar Tables or Calendar Functions, name your poison!

http://dwaincsql.com/2014/03/30/calendar-tables-in-t-sql/

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

ChrisM@Work (1/23/2015)
SELECT MondaysThisMonth = DATEADD(DAY,n,MondayBeforeFOM) FROM ( SELECT FirstOfMonth, MondayBeforeFOM = DATEADD(DAY,DATEDIFF(DAY,0,FirstOfMonth)/7*7,0) FROM (SELECT FirstOfMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) d ) e CROSS JOIN (SELECT 7 UNION ALL SELECT 14 UNION ALL SELECT 21 UNION ALL SELECT 28 UNION ALL SELECT 35) f (n)
WHERE DATEADD(DAY,n,MondayBeforeFOM) < DATEADD(MONTH,1,FirstOfMonth)

That is such an awesome script.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Gosh :blush: thanks Koen! I like what Jeff's done with it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

ChrisM@Work (1/27/2015)
Gosh :blush: thanks Koen! I like what Jeff's done with it.

All I did was tweak it. It was your idea and it was (IS) a hell of a good one!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

farooq.hbs (1/23/2015)
Hi,
[font="Arial Black"]How to retrieve only Monday dates from each month in sql server.
[/font]If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
Is that possible?
[font="Arial Black"]I want to calculate bi weekly range data in the sql.
[/font]

Alright. So you now have a solution for the first problem (bolded) above. What does that have to do with the second problem (also bolded above) and when did you want the "bi weekly range data" to start during a given year? Do you want to use the even weeks or the odd weeks and what do you want done for any given month? And what do you want to do on 53 week years or do you want it to be every other week for the rest of history and what's the first Monday that you want to start this "bi weekly range data" on?

Joe Celko is correct that one of the better "Monday" standards for weeks is the ISO system (although, leave it to the U.S. Government to not follow it for the fiscal year) but, before we go there, please answer my questions about better defining your second problem.

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

philyoungkim SSC Rookie Points: 37 More actions · Answer 13

philyoungkim

SSC Rookie

Points: 37

January 27, 2015 at 11:17 pm

#1773572

Thanks. Very helpful!

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 14

Jeff Moden (1/23/2015)
Thought I'd throw my tweaks into the ring.
DECLARE @PassedInDate DATETIME;
SELECT @PassedInDate = '15 OCT 2014'
;
SELECT Wk# = ROW_NUMBER() OVER (ORDER BY wk.MondayDate), wk.MondayDate
FROM (SELECT DATEADD(dd,DATEDIFF(dd,days.Offset,DATEADD(mm,DATEDIFF(mm,0,@PassedInDate),0))/7*7,0)
FROM (VALUES (-6),(-13),(-20),(-27),(-34))days(Offset)
)wk(MondayDate)
WHERE DATEDIFF(mm,0,wk.MondayDate) = DATEDIFF(mm,0,@PassedInDate)
;

Unfortunately the ROW_NUMBER function invokes a very (relatively) expensive SORT operator, making this method far more expensive than Chris's. To avoid this, a column can be added in the value construct which serves the same purpose.

😎

DECLARE @PassedInDate DATETIME;

SELECT @PassedInDate = '15 OCT 2014'

;

SELECT

wk.Wk#

,wk.MondayDate

FROM (

SELECT DATEADD(dd,DATEDIFF(dd,days.Offset,DATEADD(mm,DATEDIFF(mm,0,@PassedInDate),0))/7*7,0)

,Wk#

FROM (VALUES (-6,1),(-13,2),(-20,3),(-27,4),(-34,5))days(Offset,Wk#)

)wk(MondayDate,Wk#)

WHERE DATEDIFF(mm,0,wk.MondayDate) = DATEDIFF(mm,0,@PassedInDate)

;