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
I need to find MondayOfCurrentWeek for last year
SELECT DATEADD(wk, -52, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)) MondayOfCurrentWeek
I need to find Sunday, three months ago.
Thanks! Worked fine.
Hi there,
i would like to find Saturday of this week.. do u have any idea?
select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) — Saturday of the current week
hi..,
I have a doubt if i give one date and how to find Week of the Particular date ,
and How to find that date of month and Week of the month for given date
please do needful
thanks in advance..
Prakash
Trying to understand how it works
Because second parameter in DATEDIFF is datetime but int is used in here; and the third parameter for DATEADD is datetime but int is used in here. Could you please explain how it works
My input is YEAR and WEEK number, and I need the monday of this week.
ISO_WEEK:
when 1 jan is friday-saturday-sunday then the 1st week starts on the next monday, and 1 jan is on 53th week of the last year.
example 2016.01.01 is 53th week of 2015 and 1st week starts with 04 jan (monday)
— input:
declare @Year int = 2016
, @Week int = 1
— internal
declare @Year0101 date
, @FirstMonday date
set datefirst 1 — monday
select @Year0101=convert(date,convert(varchar,@year)+’.01.01′)
select @FirstMonday=dateadd(day,-1*(datepart(WEEKDAY,@Year0101)-1),@Year0101)
select dateadd(week,@week-1+(case when datepart(ISO_WEEK,@Year0101)=1 then 0 else 1 end),@FirstMonday)
Whan You want to us it in function, then You can’t change DateFirst, use this:
— input
declare @Year int = 2016
, @Week int = 1
— internal
declare @Year0101 date
, @FirstMonday date
select @Year0101=convert(date,convert(varchar,@year)+’.01.01′)
select @FirstMonday= dateadd(DAY,case when @@datefirst = 7 then 1 else -1*(@@datefirst-1) end, case when datepart(ISO_WEEK,@Year0101)=1 then dateadd(day,-1*(datepart(WEEKDAY,@Year0101)-1),@Year0101)
else dateadd(day,7+(-1*(datepart(WEEKDAY,@Year0101)-1)),@Year0101) end)
— result
select dateadd(week,@week-1,@FirstMonday)
don’t know why google keep gicing me this site as a reference for SQL… Even the simplest posts are either stolen (ie, never referencing the sources that did actually found the solution) or not checked and you need to dive in the comments to find the working solution… sigh
This will help you, If you are looking for start date & end date of the current week
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)
Thanks for Sharing @Obaid
i want to find 47th Thursday from a given year using sql statements
Hi Guys,
How would i use this formula if i need the count for the coming saturday and last saturday only? Just counts for 2 saturdays in different columns.
for example if I am checking data on any other week day between these 2 saturdays. I should be able to know the data for last sat & the coming sat.
Thanks in advance for your help,
This does not work as intended. If you put in a Sunday date it defaults to the next Monday because inherently if you use a DateDiff function SQL it assumes Sunday is the first day of the week. Here is a note from Microsoft on their DateFirst Microsoft Doc:
Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.
So you need to use something like this:
Select case
WHEN DATEADD(ww, DATEDIFF(ww,0,GetDate(), 0) >GetDate() THEN
DATEADD(ww, -1, DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0))
ELSE
DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0)
End
How does
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek
return a Monday date?
Is Sunday not the first day of a week? Should this not return the date for the first day of the current week instead?
If you use DATEPART, then it returns a Sunday date as a 1 and a Saturday date as 7.
Hello
I’m trying to return data from Monday to current day.
it is incorrect if you select date of Sunday.
Declare @a date=’12 sep 2021′
SELECT DATEADD(wk, DATEDIFF(wk,0,@a),0) MondayOfCurrentWeek
Result is showing ’13 sep 2021′