Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

First and Last day of the week in any given Date

Posted on 2006-07-15
8
Medium Priority
?
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 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