How to get the monday of every week
-
example: today (6/20) tuesday
Monday =6/19/2017
last week monday : 6/12/2017
Previous last week monday : 6/05/2017
I need to get all the monday's for last 15 monday's from getdate. -
Why not use a Calendar table?
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/Then you can filter down to what you want (like X number of Mondays), by filtering for the day and using TOP(n).
-
Try this, which uses a CTE-based calendar table:
WITH E1 AS (SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
NUMBERS AS (SELECT TOP (112) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS RN
FROM E1 AS A, E1 AS B, E1 AS C
)
SELECT TOP (15) CAST(DATEADD(day, 0 - N.RN, GETDATE()) AS date) AS THE_DATE
FROM NUMBERS AS N
WHERE DATEPART(weekday, DATEADD(day, 0 - N.RN, GETDATE())) = 2
ORDER BY DATEADD(day, 0 - N.RN, GETDATE()) DESC;Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, June 21, 2017 5:55 AM
The above depends very much on your SQL server language, so be careful here. For example, the results I get back from this are wrong (dates i get are 2017-06-20, 2017-06-13, etc, which are Tuesdays).
I've amended Steve's WHERE clause slightly (in bold), which should resolve that issue:
WITH E1 AS (SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
NUMBERS AS (SELECT TOP (112) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS RN
FROM E1 AS A, E1 AS B, E1 AS C
)
SELECT TOP (15) CAST(DATEADD(day, 0 - N.RN, GETDATE()) AS date) AS THE_DATE
FROM NUMBERS AS N
WHERE DATEPART(weekday, DATEADD(day, 0 - N.RN + (@@DATEFIRST % 7), GETDATE())) = 2
ORDER BY DATEADD(day, 0 - N.RN, GETDATE()) DESC;Cheers 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
SELECT
DateAdd(WEEK,number*-1,DateAdd(DAY, 2, DateAdd(WEEK, DateDiff(WEEK,0,GetDate()), '19000101')))
FROM master.dbo.spt_values sv
WHERE sv.type='P'
AND sv.number BETWEEN 0 AND 15
-
The method below is math only, and thus very efficient; works under all date settings; and is flexible/reusable for other days / months / etc..
DECLARE @start_date date
SET @start_date = GETDATE();WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, @start_date) % 7,
DATEADD(DAY, -7 * week#.number, @start_date)) AS Monday
FROM cteTally100 week#
WHERE week#.number BETWEEN 0 AND 15
ORDER BY Monday;SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thought I'd throw my hat into the ring. Might as well make the functionality reusable in case requirements change or you someday need to go in the other direction. Further and like Joe's and Scott's, it doesn't calculate all of the dates and then filter out everything except Mondays. It only calculates Mondays. It also doesn't rely on any DATEFIRST settings or any language specific thing unless you're using the Hijri calendar. It can also span the entire range (430,308 Mondays) of the DATETIME datatype if you need it to and does so in about 2 seconds to the screen and 69ms when dumped to a variable.
As always, details are in the comments in the code, including how to use it.
/****************************************************************************************
Purpose:
Given a number of weeks and a date, return @pweeks number of dates of Mondays starting
with the Monday of the week that @pSomeDate is a part of. Previous weeks are indicated
by the use of a negative number for @pweeks and future weeks are indicated by use of a
positive number.Usage Example:
--===== Return the PREVIOUS 15 weeks' of Mondays including this week.
SELECT MondayDT
FROM dbo.Mondays(-15,GETDATE())
;
--===== Return the FUTURE 15 weeks' of Mondays including this week.
SELECT MondayDT
FROM dbo.Mondays( 15,GETDATE())
;
--===== Return every Monday from the beginning of 1753 to the end of 9999.
SELECT MondayDT
FROM dbo.Mondays( 430308,'1753')
;
Programmer's Notes:
1. The function may be used as part of a CROSS or OUTER APPLY or as a joinable source.
2. -53690 is the date serial number of the date '1753-01-01', which is the earliest date
available to the DATETIME data type and is also a Monday.
3. The /7*7 part of the formula is integer math to correctly calculate the number of
whole weeks that have passed from '1753-01-01' up to @pSomeDate.Revision History:
Rev 00 - 21 Jun 2017 - Jeff Moden
- Initial creation and unit test.
****************************************************************************************/
(@pWeeks INT, @pSomeDate DATETIME)
RETURNS TABLE AS
RETURN WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E6(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f) --Up to 1 million
SELECT TOP (ABS(@pWeeks))
MondayDT = DATEADD( wk
,SIGN(@pWeeks)*(ROW_NUMBER() OVER (ORDER BY N)-1)
,DATEADD(dd,DATEDIFF(dd,-53690,@pSomeDate)/7*7,-53690)
)
FROM E6
;
GO--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) - Joe Torre - Wednesday, June 21, 2017 9:50 AM
SELECT
DateAdd(WEEK,number*-1,DateAdd(DAY, 2, DateAdd(WEEK, DateDiff(WEEK,0,GetDate()), '19000101')))
FROM master.dbo.spt_values sv
WHERE sv.type='P'
AND sv.number BETWEEN 0 AND 15Good code Joe. The only bugaboo in it is that the OP asked for Mondays starting with the week of the current date.
--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) -
The question that I have is, should 15 or 16 Mondays be returned?
--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) -
I was in hurry so my code isn't what I'd normally do. Normally I'd calc only the first/last Monday, then simply subtract/add 7 days to that for other dates. There's no reason to do multiple date calcs, but for only 15/16 calcs, I figured it wouldn't matter really :-).
I do know there's no chance ever that I'd use any I/O to any table to do something like that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- komal145 - Tuesday, June 20, 2017 8:59 PM
We need to do some corrections here. The only display format allowed in ANSI/ISO standard SQL is based on the ISO 8601 standard; "yyyy-mm-dd" and not the local dialect you used. SQL Server now has CURRENT_TIMESTAMP to replace the old Sybase/UNIX getdate(). The ISO 8601 standards have another format for dates; the week within year. It looks like "yyyyWww-[1-7]" which starts with the usual four digit year, as the W as a separator, the week number within the year (1 through 52 or 53) and the number of the day of the week (one is Monday, seven is Sunday). If you Google around, you can find websites with this format that you can download and put in your calendar table. This not only sought her immediate problem, but you'll find it to be incredibly useful.
Please post DDL and follow ANSI/ISO standards when asking for help.
- Jeff Moden - Wednesday, June 21, 2017 8:08 PM
Very nice! I imagine that's about as optimal performance-wise as we can get.
This next part will of course depend on the actual requirements around the use of such a function, but one potential functional concern I would have is that the function's result set always includes the most recent Monday <=@pSomeDate.
So, run right now with @pSomeDate=GETDATE(), both negative and positive values for @pweeks will include June 19th of this year in the result set. If we interpret negative @pweeks as requesting some number of previous Mondays, and positive @pweeks as requesting some number of upcoming Mondays, this is an odd result.
That behavior can be changed pretty easily, but I just figured I'd point it out.
Cheers!
EDIT: Tweaked some wording.
- jcelko212 32090 - Thursday, June 22, 2017 9:02 AM
ANSI/ISO is NOT the only format in town. Is it the "best"? It depends. You and I agree that, for most things, it is. For normal humans, they want to see it the way they want to see it. Fortunately, none of the solutions presented have done any formatting. 😉
--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) - Jacob Wilkins - Thursday, June 22, 2017 9:07 AM
Thanks for the feedback and you're absolutely correct. It should have a +/- offset for which Monday to start with. It should also be tweaked to allow which day of the week to return because, even in the U.S., there are many standards as to what a week is. Heh... that's the fun part about standards... there are so many to choose from for the same thing. 😉
--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 - Thursday, June 22, 2017 11:33 AM
>>Heh... that's the fun part about standards... there are so many to choose from for the same thing. <<
That quote goes back to Dyson (maybe before her). In my book on Measurements and Standards, chapter 8 is on sex codes. You might want to use more "medical" or "biological" codes that certain circumstances. The Center for Disease Control, NETSS, NCHS, ECML, NCVHS, JC3IEDM, ICAO and NAACCR specialized for their disciplines. The ZIMS (zoological information management system) includes combinations that do not occur in mammals. It kinda makes you feel like you're a very vanilla animal in the scheme of things.
Most of the world uses ISO 5218 (0 = unknown, 1 = male, 2 = female, 9 = lawful person), and this lets us exchange data. This is why competent professionals use ANSI/ISO standards the database, and translate into local dialect as required by regulations in the front end (presentation layer).
Someday soon,we might move to ASTM E1633, is specified for electronic health records. For example, you might be coded "MP" and not be a Member of Parliament, but actually a "male pseudo-hermaphrodite" instead.). .
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply