Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

First and Last day of the week in any given Date

Posted on 2006-07-15
8
Medium Priority
?
273 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
6 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

564 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