Today I will explain how you get the Start Date and End Date of week from any date.One of my colleague has requirement something like that he has week no and he want the start date and End date of that week.
There are many ways to find that solution but I got one solution that is useful to you if you want something like that in future.
Below is the Query from that you can get the start date and end date of the week.In this Query Replace order date with any date and you get the results from this query.
SELECT
CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST – DATEPART(dw, orderdate) – 6, orderdate)), 101) startdate
CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST – DATEPART(dw, orderdate), OrderDate)), 101) enddate,
from tbl_dietorder_trn (Any Table )
You can use Getdate() function and check this.
In this I use the @@DATEFIRST Function. By using this function you can set the first day of week. Sunday is default first day of week so it’s default value is 7.
This Query to find start date and End date id different from other because of @@DATEFIRST function.
You can set that function value between 1 to 7.Base on that thing this query is give you the result.
1 – Monday
2 – Tuesday
3 – Wednesday
4 – Thursday
5 – Friday
6 – Saturday
7 – Sunday
You can set the value of @@DATEFIRST.
Set Datefirst 1
You can get this information from below link about @@DATEFIRST Function.
http://msdn.microsoft.com/en-us/library/ms187766.aspx
my question to you,
can any one know other than this Query that give First and Last Date of week?
I hope this is useful to you in future.Please give your feedback.
SQL HELPER 