Improve company productivity with a Business Account.Sign Up

x
?
Solved

First and Last day of the week in any given Date

Posted on 2006-07-15
8
Medium Priority
?
274 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

607 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