Click here to Skip to main content
15,880,299 members
Please Sign up or sign in to vote.
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
Updated 31-Dec-21 5:00am

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
 
Share this answer
 
v2
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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900