Getting First and Last Date of the Current Week in sql.

This Post will Explain how we can get the First Date and Last Date of the Current Week or for a given random date if Supplied in sql server.
Here in the Example Below getdate() function is used to get the  todays date. You can make it input field in stored procedure to generalize this procedure for any date and not just current date.

DateADD(interval,increment int,date) Function adds the increment int no of days to the date times the interval, here interval is d which days. Hence 1-datepart(dw,getdate()) no of days are added to the current date.

DatePart(interval,date) gets the date part in interval provided. In our example interval is week day dw hence, datepart gets the what date (1-7) in week is for the given date.


in our example we add the number of day of the week for the date subtracted from 1 for first day which will result in subtracting 2 (1-3) days if today is 3rd day of the week (Tuesday) resulting in sunday's date.
for last day of the week 7-3 will be 4 a positive no which will be added to todays date resulting in saturdays date.

Example:

DECLARE @Startdt Date
DECLARE @Enddt Date
DECLARE @temptbl Table(StartDate Date, EndDate Date)
SET @Startdt= dateadd(d,1-datepart(dw,getdate()),getdate())
SET @Enddt =dateadd(d,7-datepart(dw,getdate()),getdate())

INSERT INTO @temptbl VALUES(@Startdt,@Enddt)
SELECT * FROM @temptbl