Very Simple Script which find Monday of the Current Week

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek

Reference : **Pinal Dave (****http://blog.SQLAuthority.com****)**

Very Simple Script which find Monday of the Current Week

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek

Reference : **Pinal Dave (****http://blog.SQLAuthority.com****)**

%d bloggers like this:

Your articles are very helpful. Thanks for the public service.

LikeLike

Your solutions are extremely useful. Thanks a ton brother

LikeLike

Dear Pinal Dev…

I have never found a simpler scripts in date functions any where in the world … Hats offf….

LikeLike

simple, to-the-point answers – perfect every time.

Thanks a bunch! :)

LikeLike

Awesome! Perfect solution as always! Many thanks…..

LikeLike

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)

:-)

LikeLike

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)

LikeLike

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)

LikeLike

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.

:)

LikeLike

How to find Monday of last week?

LikeLike

@cimnil

Find Monday of this week , and use DATEADD to subtract 7 days.

LikeLike

Perfect job. Thanks

LikeLike

“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

LikeLike

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”.

LikeLike

i would like to find Saturday of this week.. do u have any idea?

LikeLike

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)

this finds Monday of the current week and then subtracts 1 day. Cheers!

LikeLike

Thanks Nate.

LikeLike

this works perfectly. thank you.

LikeLike

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?

LikeLike

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.

LikeLike

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!

LikeLike

nice piece of script. simple yet effective!

LikeLike

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

LikeLike

Try this

select dateadd(week,datediff(week,0,getdate()),0)+2

LikeLike

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’))

LikeLike

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);

LikeLike

how to find next week your birthday alert using sql

LikeLike

You ROCK !!!!!! A Big Thumbs Up

LikeLike

I need to find MondayOfCurrentWeek for last year

LikeLike

I need to find Sunday, three months ago.

LikeLike

Thanks! Worked fine.

LikeLike

Hi there,

i would like to find Saturday of this week.. do u have any idea?

LikeLike

select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) — Saturday of the current week

LikeLike

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

LikeLike

Trying to understand how it works

LikeLike

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

LikeLike

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)

LikeLike

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)

LikeLike

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

LikeLike

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)

LikeLike

Thanks for Sharing @Obaid

LikeLike

i want to find 47th Thursday from a given year using sql statements

LikeLike

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,

LikeLike