Very Simple Script which find Monday of the Current Week
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek
Reference : Pinal Dave (https://blog.sqlauthority.com)
SQL SERVER – Find Monday of the Current Week
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
48 Comments. Leave new
Your articles are very helpful. Thanks for the public service.
Your solutions are extremely useful. Thanks a ton brother
Dear Pinal Dev…
I have never found a simpler scripts in date functions any where in the world … Hats offf….
simple, to-the-point answers – perfect every time.
Thanks a bunch! :)
Awesome! Perfect solution as always! Many thanks…..
This does not work. Here is an example:
DECLARE @TODAY DATETIME
SET @TODAY = ‘June 14, 2009’
SELECT
@TODAY AS TODAY,
LAST_MONDAY = DATEADD(wk, DATEDIFF(wk,0,@TODAY), 0)
:-)
Last Monday (eg Monday of this week if it is after Monday or Monday of last week if it is Sunday)
DATEDIFF(day, 0, CAST(@TODAY as DATETIME) – DATEDIFF(day, 0, @TODAY) %7)
try this,
declare @date datetime = ‘2015-01-8’
select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date – 1) + 1 as ‘Current Week of this month’
,
@date AS TODAY,
LAST_MONDAY = DATEADD(wk, DATEDIFF(d, 0, @date) / 7, 0),
[LAST_MONDAY’s SUNDAY] = DATEADD(wk, DATEDIFF(d, 0, @date) / 7, 6)
I believe that is because Sunday is classed as the first day of the week.
Therefore when the date is a Sunday, the Monday of the current week is tomorow.
:)
How to find Monday of last week?
@cimnil
Find Monday of this week , and use DATEADD to subtract 7 days.
Perfect job. Thanks
“I believe that is because Sunday is classed as the first day of the week.”
If I set Monday to be the first day of the week it still doesn’t work.
SET DATEFIRST 1;
DECLARE @TODAY DATETIME ;
SET @TODAY = ‘June 14, 2009’ ;
SELECT
@TODAY AS TODAY,
LAST_MONDAY = DATEADD(wk, DATEDIFF(wk,0,@TODAY), 0)
returns:
TODAY LAST_MONDAY
2009-06-14 00:00:00.000 2009-06-15 00:00:00.000
Try this:
DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7, 0).
This works because 1/1/1900 (i.e. ‘0’) was a Monday so you are basically dividing the days since then by 7 days exactly.
FYI – you can also find the day of the week based on a Monday start date (i.e. Monday = 0, Tuesday = 1, etc.) by using “% 7” instead of “/ 7”.
i would like to find Saturday of this week.. do u have any idea?
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)
this finds Monday of the current week and then subtracts 1 day. Cheers!
Thanks Nate.
this works perfectly. thank you.
The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn’t return the date of last monday.
I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.
what if I want to run a script in any time and the result must return the date of last monday, last tuesday til last friday.
Any way to do it?
The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn’t return the date of last monday.
I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.
Any query can give me the date of last monday til last friday, this mean I need 5 days no matter what day I run the script.
Pinal Dave – Thanks SO much – I have seen your posts before but this one gave me exactly what I wanted. and so simple… a summary for the week beginning on monday was MUCH simpler with this small bit of code! you are the BEST!
nice piece of script. simple yet effective!
I tried to find Wednesday of the current week on the same lines, but was not successful. Can you pl. help.
For any given date, I need the Wednesday of that week.
Thanks in advance,
GS
Try this
select dateadd(week,datediff(week,0,getdate()),0)+2
Hi There,
Dave’s tit bit is perfect for defalut date caliculations. i.e. by defauls Sunday is considerded as first day of week in US.
if you want to consider monday as first day of the week you may use the below tit bit.
SET DATEFIRST 1
select (DateAdd(d, – (DatePart(dw,’2011-08-21′) -1), ‘2011-08-21’))
Since you helped me out here, I’ll pass along what it was exactly you helped me with. This query builds a result set that lists all the weeks in the past year for a view or function:
WITH CTE_PastYearinWeeks
AS (
select
/* Thanks to SQL Authority for below. It gets the number of weeks between now and the beginning of (sql) time and adds it to the beginning of (sql) time, thus setting it to the first day of the week! */
DATEADD(wk, DATEDIFF(wk,0,GETDATE()-365), 0) as DateStart
UNION ALL
SELECT DATEADD(WK,1,DateStart)
FROM CTE_PastYearinWeeks
WHERE DATEADD(WK,1,DateStart) <= GETDATE()
–and DATEPART(YYYY,DateStart) <= DATEPART(YYYY,GETDATE())
)
select DATEPART(YYYY,DateStart) as YearNum
, DATEPART(WK,DateStart) as WeekNum
, SUBSTRING(CONVERT(varchar, DateStart, 107),1,6) + ' – ' + CONVERT(varchar, DATEADD(DD,6,DateStart), 107) as WeekTitle
from CTE_PastYearinWeeks
OPTION (MAXRECURSION 0);
how to find next week your birthday alert using sql
You ROCK !!!!!! A Big Thumbs Up