October 26, 2010 at 10:18 am
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
October 26, 2010 at 10:42 am
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
October 26, 2010 at 10:47 am
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
October 26, 2010 at 11:20 am
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
October 26, 2010 at 11:50 am
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.
October 26, 2010 at 2:32 pm
With a few changes you can have up to 12 years.
With a real tally table[/url] 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
October 27, 2010 at 6:32 am
Thank you Gianluca, i really appreciate you.
October 27, 2010 at 9:48 am
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
October 27, 2010 at 10:27 am
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
October 27, 2010 at 1:23 pm
Keep it simple and avoid any reference to SET DATEFIRST!
DECLARE@Year SMALLINT = 2011
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,
DATEADD(MONTH, 12 * @Year + number - 22800, -1) AS LastOfMonth
FROMmaster..spt_values
WHERETYPE = 'P'
AND number BETWEEN 1 AND 12
)
SELECTDATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROMcteCalendar
N 56°04'39.16"
E 12°55'05.25"
October 27, 2010 at 3:22 pm
Nice one, Peso!
Well, I wouldn't say "simple", I would say "short" instead! 🙂
Very nicely done, as usual.
-- Gianluca Sartori
October 27, 2010 at 4:08 pm
SwePeso (10/27/2010)
Keep it simple and avoid any reference to SET DATEFIRST!
DECLARE@Year SMALLINT = 2011
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,
DATEADD(MONTH, 12 * @Year + number - 22800, -1) AS LastOfMonth
FROMmaster..spt_values
WHERETYPE = 'P'
AND number BETWEEN 1 AND 12
)
SELECTDATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROMcteCalendar
Peso, I agree. Very short and sweet, with a beautiful execution plan to boot! The select from the CTE is doing a beautiful calculation to determine the appropriate day of the week.
As a result of dissecting this (in order to understand it) I have a few questions and observations.
1. SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,
This is actually returning everything as the 7th of the month. The 6 needs to be changed to 0 to make it return the 1st.
This means that the first 6 needs to be changed to a 0 in this line also:
SELECTDATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
2. (In the first bit of code I quoted above) I assume that the reason you're using MONTH is because that is the shortest interval with a consistent # over the course of a year. Days may have 365 or 366 in a year; Weeks may have 52 or 53 in a year.
3. (In the same first bit of quoted code above) I completely do not understand what the numerical calculation is doing. 12*2011+number(1-12)-22801 returns the first of the month for each month of the specified year. 22801 corresponds to a date of Feb 01, 3800. So, where does 22801 come from? How did you come up with this?
Like I said, I'm just trying to understand what is going on here.
FWIW, I modified your code into something that makes more sense to me. However, it adds an additional Compute Scalar to the execution plan, and I don't see why. I also added in the OPs request for being able to specify the # of years.
DECLARE @Year SMALLINT,
@Date datetime,
@NbrYears tinyint;
SET @Year = 2010;
SET @Date = CONVERT(char(4),@Year)+'0101';
SET @NbrYears = 3;
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
SELECT DATEADD(MONTH, number-1, @Date) AS FirstOfMonth,
DATEADD(MONTH, number, @Date)-1 AS LastOfMonth
FROM master..spt_values
WHERE TYPE = 'P'
AND number BETWEEN 1 AND (12 * @NbrYears)
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday,
FirstOfMonth,
LastOfMonth
FROM cteCalendar
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 27, 2010 at 4:46 pm
22800 = 1900 * 12
so
DATEADD(MONTH, 12 * @Year + number - 22801, 6)
is equivalent to
DATEADD(MONTH, 12 * (@Year - 1900) + number - 1, 6)
but saves 1 addition operation.
October 27, 2010 at 11:16 pm
WayneS (10/27/2010)
This is actually returning everything as the 7th of the month. The 6 needs to be changed to 0 to make it return the 1st.
If you do, you can end up with a date from previous month.
This can happen due to the integer division by 7.
So, if I instead start with the seventh day of the month, I am guarateed that the first sunday is within these seven days, even after the integer division. I am doing the same with the LastOfMonth.
FirstOfMonth doesn't imply the first day... 😉
N 56°04'39.16"
E 12°55'05.25"
October 28, 2010 at 1:05 am
andrewd.smith (10/27/2010)
...but saves 1 addition operation.
Doesn't save a lot of headscratching 😉
Peso's code is incredibly smart. Full stop.
If there's one minor thing that can be criticized, personally, I prefer code that can easilly be understood and maintained. When I add something "obscure" to my code, I tend to comment it. Probably because I'm a bit thick-headed, but also because people with a thicker head than mine could have to change it some day.
His code is much more efficient than the one I posted because the execution plan is far less demanding: it doesn't need to save 1 microsecond avoiding 1 addition operation. The microsecond you save today could turn into hours lost next year trying to understand what the code does. The fact itself that an experienced developer like Wayne got in trouble with it should be sufficient to make the point.
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply