Current Day of the week from Current week of the year

Transact-SQL
1

I need help to find current day from current week of the year, and start and end dates of the current week.
For eg – Today is Monday, April 8th, 2019. 1st day of the week.
This is the 15th week. I am able to find this individually, but would like 1 SQL statement if possible.

My output should be 1, 04/08/2019, 04/14/2019.

2

Depending on where you are, Sunday is first day of week, not Monday

declare @d date = getdate()

select @d, DATEPART(dw, @d), DatePart(Week, @d)

3

I used @base_date only so that it's easy to test for dates other than today, rather than hard-coding GETDATE() in all the calcs:

DECLARE @base_date date
DECLARE @most_recent_Monday date

SET @base_date = GETDATE()

SET @most_recent_Monday = DATEADD(DAY, -DATEDIFF(DAY, 0, @base_date) % 7, CAST(@base_date AS date))

SELECT @most_recent_Monday AS most_recent_Monday, (DATEDIFF(DAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, @base_date), 0), @most_recent_Monday) + 1)/7 + 1 AS week#
4

hi

doing my way of solution !!!! something different

please click arrow to the left for Drop CREATE Data 
drop table #sample_data 
go 


create table #sample_data 
(
date_ok date 
)
go 

insert into #sample_data select '2019-10-09'
insert into #sample_data select '2019-04-19'
insert into #sample_data select '2019-12-02'
go

image

select   
         *
       , DATEADD(dd, -(DATEPART(dw, date_ok)-1), date_ok) [WeekStart]
	   , DATEADD(dd, 7-(DATEPART(dw, date_ok)) , date_ok) [WeekEnd] 
from 
   #sample_data
go

image

5

Responding to 9-month-old things is bad enough, but the code you posted is dependent on a specific setting of "DATEFIRST" and thus returns the wrong start-of-week date for me. The code I posted earlier works the same under any/all datefirst settings. What is the point of your post?

1 Like
6

hi Scott

I am just ... practicing my TSQL ....
It may be right .. wrong ...Or somewhat working !!!!!
Something different !!!
Does not matter ... this is not PRODUCTION Support !!!

I did not DIG into all the details .. NOR did I pay attention to all the things ( previous posts )
I CAN CAN get into granular details ..very very minute details ... that expert of experts
also cannot think of
I know HOW !!!
Not only me ANYBODY CAN ... ANYBODY with interest CAN

Please excuse me if I have offended you ..why a negative TONE !!!

Very very sorry :slight_smile: :slight_smile:

7

I'm upset because the point of leaving a q like this out there is so that later someone else can use the same solution in some way.

When you come along months later and add inaccurate things to the thread, you could cause someone to use wrong code instead of correct code.

If you just want to practice, fine, create your own questions to do that.

8

Since you said you're learning, you should be incredibly happy that someone took the time to respond and tell you of the grievous error you made.

Also, you say "Does not matter ... this is not PRODUCTION Support !!!". It DOES matter a whole lot especially since this is a forum where people who may not know better might actually see what your current reputation is and just assume that you're right. You also say that you didn't dig into all the details nor did you pay attention to things. Now, even though Scott's post was aggravating and maybe even out of line in your opinion, he is absolutely correct. So... stop making excuses about what you didn't but should have done (and you MUST do those things because people are depending on it), realize that Scott may have been out of line but is still absolutely correct, and be humble enough to say "Thanks... I didn't know that".

If it turns out that you actually already knew that, then shame on you for not doing it the right way. :wink:

Ok... everyone kiss and make up. We're all in this together. :smiley:

1 Like