Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


get the first Sunday and last Saturday


get the first Sunday and last Saturday

Author
Message
jagadeeps
jagadeeps
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 374
hI,
can anyone help me how to get the first Sunday and last Saturday of every month for a year.
i need to fetch the dates for the the first Sunday and last Saturday of every month and insert into a column.

please do the needful
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38912
here's the code to get the first sunday of this month;
the last sunday of the previous month is the same date minus 7 days.
you could change the code to get the first Saturday of a given month,a dn then subtract 7 days;

--first sunday of this month....
-1=sunday,-2=saturday,-3-friday etc
SELECT
datename(dw,dateadd(dd,-1,DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0))),
dateadd(dd,-1,DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0))



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5687 Visits: 25280
Look at this, and with slight modification you should be able to do what you require

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5728 Visits: 13305
This should fo the trick for you:


-- Set year in a variable
DECLARE @Year int
SET @Year = 2010

;WITH Months AS (
   -- Create a month numbers CTE
   SELECT 1 AS MonthNumber
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9
   UNION ALL SELECT 10
   UNION ALL SELECT 11
   UNION ALL SELECT 12
   UNION ALL SELECT 13
),
Dates AS (
   -- Find first day of month
   SELECT monthNumber,
      firstDayOfMonth = DATEADD(month, monthNumber - 1, CONVERT(datetime, CAST(@Year as char(4)) + '0101', 112))
   FROM Months
),
MonthRange AS (
   -- Find last day of month
   SELECT *, lastDayOfMonth = (
      SELECT TOP 1
         DATEADD(day, -1, firstDayOfMonth)
      FROM Dates
      WHERE MonthNumber = D.MonthNumber + 1
   Wink
   FROM Dates AS D
   WHERE monthNumber <= 12
)
SELECT *, firstSunday = (
      SELECT TOP 1
         DATEADD(day, monthNumber -1, firstDayOfMonth)
      FROM Months
      WHERE DATEPART(weekday, DATEADD(day, monthNumber -1, firstDayOfMonth)) = 1
      ORDER BY monthNumber
   Wink,
   lastSaturday = (
      SELECT TOP 1
         DATEADD(day, (-1) * (monthNumber -1), lastDayOfMonth)
      FROM Months
      WHERE DATEPART(weekday, DATEADD(day, (-1) * (monthNumber -1), lastDayOfMonth)) = 7
      ORDER BY monthNumber
   Wink
FROM MonthRange



Hope this helps
Gianluca

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
jagadeeps
jagadeeps
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 374
Thank you Gianluca, you code fulfilled my requirement.

i need to knon one more thing, i replaced monthNumber <= 24 for getting two years data but it didn't work can you help me in getting the same.
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5728 Visits: 13305
With a few changes you can have up to 12 years.
With a real tally table you could have all the years you want.


-- Set year in a variable
DECLARE @StartYear int
DECLARE @NumYears int

SELECT @StartYear = 2010, @NumYears = 2

;WITH SmallTally AS (
-- Create a month numbers CTE
SELECT 1 AS N
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
),
Months AS (
   SELECT N AS MonthNumber
   FROM SmallTally
),
Years AS (
   SELECT @StartYear + N - 1 AS YearNumber
   FROM SmallTally
   WHERE N <= @NumYears
),
Dates AS (
-- Find first day of month
SELECT monthNumber, YearNumber,
firstDayOfMonth = DATEADD(month, monthNumber - 1, CONVERT(datetime, CAST(YearNumber as char(4)) + '0101', 112))
FROM Months, Years
),
MonthRange AS (
-- Find last day of month
SELECT *, lastDayOfMonth = (
SELECT DATEADD(day, -1, firstDayOfMonth)
FROM Dates
WHERE monthNumber = D.MonthNumber + 1
               AND YearNumber = YEAR(D.firstDayOfMonth)
)
FROM Dates AS D
WHERE monthNumber <= 12
)
SELECT *, firstSunday = (
SELECT TOP 1
DATEADD(day, N -1, firstDayOfMonth)
FROM SmallTally
WHERE DATEPART(weekday, DATEADD(day, N -1, firstDayOfMonth)) = 1
ORDER BY N
),
lastSaturday = (
SELECT TOP 1
DATEADD(day, (-1) * (N -1), lastDayOfMonth)
FROM SmallTally
WHERE DATEPART(weekday, DATEADD(day, (-1) * (N -1), lastDayOfMonth)) = 7
ORDER BY N
)
FROM MonthRange
ORDER BY firstDayOfMonth



--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
jagadeeps
jagadeeps
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 374
Thank you Gianluca, i really appreciate you.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6222 Visits: 10398
Gianluca's solution is missing one minor, easily overlook thing. Easily fixed with four lines of code:
-- at the top of the code
DECLARE @OldDateFirst tinyint;
SET @OldDateFirst = @@DateFirst;
SET DATEFIRST 7;

-- put at the end of the code to restore:
SET DATEFIRST @OldDateFirst;



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5728 Visits: 13305
Nice catch, Wayne.
An alternative way could be checking the results of DATEPART(weekday) for known dates and replace the constants 1 and 7 with the variables.

DECLARE @Sunday int, @Saturday int
SELECT @Sunday = DATEPART(weekday, '20101031'), @Saturday = DATEPART(weekday, '20101030')



--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 3431
Keep it simple and avoid any reference to SET DATEFIRST!


DECLARE   @Year SMALLINT = 2011

;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
   SELECT   DATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,
      DATEADD(MONTH, 12 * @Year + number - 22800, -1) AS LastOfMonth
   FROM   master..spt_values
   WHERE   TYPE = 'P'
      AND number BETWEEN 1 AND 12
)
SELECT   DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
   DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROM   cteCalendar




N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search