SQL Clone
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
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 383
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
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70066 Visits: 40923
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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15409 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
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23447 Visits: 13362
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
)
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
),
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
)
FROM MonthRange



Hope this helps
Gianluca

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
jagadeeps
jagadeeps
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 383
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.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23447 Visits: 13362
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
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 383
Thank you Gianluca, i really appreciate you.
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21184 Visits: 10652
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

Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23447 Visits: 13362
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 Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9393 Visits: 3433
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