Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

get the first Sunday and last Saturday Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 10:18 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, March 6, 2014 4:04 PM
Points: 659, Visits: 135
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
Post #1010974
Posted Tuesday, October 26, 2010 10:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1011002
Posted Tuesday, October 26, 2010 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:27 PM
Points: 5,571, Visits: 24,783
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
Post #1011007
Posted Tuesday, October 26, 2010 11:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1011030
Posted Tuesday, October 26, 2010 11:50 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, March 6, 2014 4:04 PM
Points: 659, Visits: 135
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.
Post #1011054
Posted Tuesday, October 26, 2010 2:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1011140
Posted Wednesday, October 27, 2010 6:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, March 6, 2014 4:04 PM
Points: 659, Visits: 135
Thank you Gianluca, i really appreciate you.
Post #1011465
Posted Wednesday, October 27, 2010 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:55 PM
Points: 6,582, Visits: 8,860
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
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
Post #1011668
Posted Wednesday, October 27, 2010 10:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1011710
Posted Wednesday, October 27, 2010 1:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
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"
Post #1011841
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse