Solved

First and Last day of the week in any given Date

Posted on 2006-07-15
8
256 Views
Last Modified: 2008-02-26
I want to get the first and the last day of the week by giving any date, in my case our first day start from saturday.

for example if i give 18/07/2006 it should return 15 for the first day of the week and 21 as the last day of the week.

Kind regards,
0
Comment
Question by:altuwairqi2
  • 4
  • 2
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17114506
Something like this:

Declare      @Dat datetime
Set @Dat = '2006-07-18'


Declare @DateFirst tinyint

Set @DateFirst = @@DATEFIRST    -- Save old @@DATEFIRST value
SET DATEFIRST 6

Select      DATEADD(day, -DATEPART(weekday, @Dat) + 1, @Dat) FirstDayOfWeek,
      DATEADD(day, 7 - DATEPART(weekday, @Dat), @Dat) LastDayOfWeek
SET DATEFIRST @DateFirst

Select @@DATEFIRST    -- Restore old @@DATEFIRST value
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17114521
This might be tad simpler:

Declare      @Dat datetime
Set @Dat = '2006-07-18'

Select      DATEADD(day, 7 - @@DATEFIRST -DATEPART(weekday, @Dat), @Dat) FirstDayOfWeek,
      DATEADD(day, 6 - DATEPART(weekday, @Dat), @Dat) LastDayOfWeek


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 17114541
Oops, I forgot to allow for a different @@DATEFIRST in the second expression, it should be:

Select      DATEADD(day, 7 - @@DATEFIRST - DATEPART(weekday, @Dat), @Dat) FirstDayOfWeek,
      DATEADD(day, 13 - @@DATEFIRST - DATEPART(weekday, @Dat), @Dat) LastDayOfWeek

Sorry for the multiple posts.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 17114542
Hi altuwairqi2,


CREATE FUNCTION [dbo].[ufn_GetLastDayOfWeek] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
    SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
    RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate)+6, @pInputDate)
END
GO


select [dbo].[ufn_GetLastDayOfWeek]('20060716')



Aneesh R!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17594867
dont you think splitting the points ...
:(
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17597781
Fine with me.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now