Search This Blog

Converting Weekday dates to Weekend dates in SQL

I was recently asked by one of my FB Group user that “Is there any way to convert week day to that week end Saturday for example- 1 Jan 2016 is Friday so it should come as 2 Jan 2016 i.e. Saturday, other example like 1 Feb 2016 is Monday so it should come as 6 Feb 2016 i.e. Saturday” likewise his table was having multiple dates which has to be converted to that week Saturday.

Below is the test table

Create table Test(ID  int IDENTITY(1,1), StartDate Date)
Insert into Test VALUES ('1-1-2016'),('2-2-2016'),('10-10-2016'),('2-10-2016'),('1-13-2016')

SELECT *,DATENAME(WEEKDAY,STARTDATE) as [DayofWeek] FROM Test


If you will run the below query you will see the Result as below I have used datename function to extract the weekday from StartDate Column.

Now as per the user Requirement the Start Date Columns dates has to be converted to its respective weekend i.e. Saturday

Below is the code

Select *,Dateadd(day,(7- DATEPART(dw,startdate)),StartDate)as EndofWeek
from Test

--The above query has given us the date as required,
--In order to verify that all EndofWeek extracted should be saturday Just run the below code

Select ID,StartDate,DATENAME(WEEKDAY,STARTDATE) as Startdateday, Dateadd(day,(7- DATEPART(dw,startdate)),StartDate)as EndofWeek ,Datename(DW,Dateadd(day,(7- DATEPART(dw,startdate)),StartDate))as EndofWeekDay

from Test

1 comment:

  1. Thank you for putting up a descriptive post on a very useful aspect of SQL. This actually helped me a lot to understand this topic.

    SSIS PostgreSql Write

    ReplyDelete