How do I get a week start date and end date from the date provided

Question

0.00/5 (No votes)

See more:

How can i get the StartDate,Endate,Week number from the Date providing.
* week startDate should be saturday
* week endDate should be friday
* week number should be based on the week start and end date we are getting
* also need next 3 weeks startDate and endDate

What I have tried:

select cast((getdate() - 6) AS DATE)as startDate,cast((getdate()-1 ) AS DATE) as endDate;
select cast((getdate()) AS DATE)as startDate,cast((getdate()+ 6) AS DATE) as endDate;

select DATENAME(ww, getdate());

Posted 30-Dec-21 21:29pm

Member 14978771

Updated 31-Dec-21 5:00am

Add a Solution

2 solutions

Solution 2

In addition to the solution offered by @luc-pattyn I would advise you to set up a date dimension table on your database, so that you are not calculating these dates on the fly

Quote:
While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes.

(https://www.sqlshack.com/designing-a-calendar-table/[^])
See also Creating a date dimension or calendar table in SQL Server[^]

Posted 31-Dec-21 5:00am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luc Pattyn · Accepted Answer · 2021-12-31T03:26:00

T-SQL provides a lot of functions; for each of them see the doc (google TSQL functionname).

Here is an example that should get you going.

Warning: I explicitly set DATEFIRST as per your requirement; it is essential for obtaining the results you want.

Without it, it is also possible but more complex (requires either modulo arithmtic or some case constructs), I would not go for that.

You can run the below code inside Visual Studio (using menu Tools/SQL Server)

T-SQL

DECLARE @mydate DATE = DATEFROMPARTS(2022,1,1);
DECLARE @startdate DATE;
DECLARE @enddate DATE;
DECLARE @wd INT;
DECLARE @wk INT;
DECLARE @dd INT = 1;
-- make saturday the first day of the week (1=monday , 7=sunday)
SET DATEFIRST 6;
PRINT @@DATEFIRST
PRINT ' '
WHILE @dd < 40
BEGIN
    -- PRINT @mydate 
    SET @wd=DATEPART(weekday, @mydate)
    -- PRINT @wd
    SET @startdate = DATEADD(day, 1-@wd, @mydate)
    -- PRINT @startdate 
    SET @enddate = DATEADD(day, 7-@wd, @mydate)
    -- PRINT @enddate 
    SET @wk = DATEPART(week, @mydate)
    -- PRINT @wk
    PRINT convert(varchar(10),@mydate)+' start='+convert(varchar(10),@startdate)+' end='+convert(varchar(10),@enddate)+' week='+convert(varchar(2),@wk)
    PRINT ' '
    SET @mydate = DATEADD(day, 1, @mydate)
    SET @dd = @dd + 1;
END;

The results would look like this:

6
 
2022-01-01 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-02 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-03 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-04 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-05 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-06 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-07 start=2022-01-01 end=2022-01-07 week=1
 
2022-01-08 start=2022-01-08 end=2022-01-14 week=2
 
2022-01-09 start=2022-01-08 end=2022-01-14 week=2

...

Finally you can convert such code to a single-line SQL e.g.

SET DATEFIRST 6; DECLARE @mydate DATE = DATEFROMPARTS(2022,1,12); DECLARE @wd INT=DATEPART(weekday, @mydate); SELECT DATEADD(day, 1-@wd, @mydate) as startdate,
DATEADD(day, 7-@wd, @mydate) as enddate,  DATEPART(week, @mydate) as week