September 13, 2013 at 1:25 am
Hi all,
Please help me to find out 2nd and 4th saturdays of current year.
Thanks and Regards
Shirish Phadnis
September 13, 2013 at 1:41 am
Here you go:
-- Set the first day of the week to Monday
SET DATEFIRST 1;
-- create the current year
DECLARE @YearStartDATE = CONVERT(DATE,CONVERT(CHAR(4),YEAR(GETDATE())) + '-01-01');
DECLARE @YearEndDATE = CONVERT(DATE,CONVERT(CHAR(4),YEAR(GETDATE())) + '-12-31');
WITH CTE_FullYear AS
(
SELECT TOP (366) DATEADD(dd,[number],@YearStart) dates
FROM master.dbo.spt_values
WHERE [type] = 'P' AND [number] >= 0 AND [number] < 367 -- account for leap years
AND DATEADD(dd,[number],@YearStart) <= @YearEnd
ORDER BY number
)
SELECT dates
FROM
(
SELECT
dates
,[Month]= MONTH(dates)
,RID= ROW_NUMBER() OVER (PARTITION BY MONTH(dates) ORDER BY dates)
FROM CTE_FullYear
WHERE DATEPART(dw,dates) = 6 -- select saturdays
) tmp
WHERE RID IN (2,4);
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2013 at 2:03 am
DECLARE @StartDate DATETIME;
SELECT @StartDate = DATEADD(year, DATEDIFF(year,0,GETDATE()), 0);
-- This clause is just to get some numbers: a Tally table, for joining to get a range of dates
WITH NumDays AS (
SELECT TOP 365 -- one year of days
ROW_NUMBER() OVER (ORDER BY sc1.ID) AS N
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
UNION SELECT 0 -- just to get a zero in the results
), DateRange AS (
SELECT N,
DATEADD(day, N, @StartDate) AS [Date],
DATEPART(dw,DATEADD(day, N, @StartDate)) AS [Day],
DATENAME(dw,DATEADD(day, N, @StartDate)) AS [DayName],
DATENAME(week,DATEADD(day, N, @StartDate)) AS [WeekNo]
FROM NumDays
)
SELECT *
FROM DateRange
WHERE [DayName] = 'Saturday'
AND WeekNo in (2,4)
September 13, 2013 at 2:22 am
Tom Brown (9/13/2013)
DECLARE @StartDate DATETIME;
SELECT @StartDate = DATEADD(year, DATEDIFF(year,0,GETDATE()), 0);
-- This clause is just to get some numbers: a Tally table, for joining to get a range of dates
WITH NumDays AS (
SELECT TOP 365 -- one year of days
ROW_NUMBER() OVER (ORDER BY sc1.ID) AS N
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
UNION SELECT 0 -- just to get a zero in the results
), DateRange AS (
SELECT N,
DATEADD(day, N, @StartDate) AS [Date],
DATEPART(dw,DATEADD(day, N, @StartDate)) AS [Day],
DATENAME(dw,DATEADD(day, N, @StartDate)) AS [DayName],
DATENAME(week,DATEADD(day, N, @StartDate)) AS [WeekNo]
FROM NumDays
)
SELECT *
FROM DateRange
WHERE [DayName] = 'Saturday'
AND WeekNo in (2,4)
This only returns the 2 saturdays from January.
I modified my original query to your idea (using date functions instead of row numbers):
-- Set the first day of the week to Monday
SET DATEFIRST 1;
-- create the current year
DECLARE @YearStartDATE = CONVERT(DATE,CONVERT(CHAR(4),YEAR(GETDATE())) + '-01-01');
DECLARE @YearEndDATE = CONVERT(DATE,CONVERT(CHAR(4),YEAR(GETDATE())) + '-12-31');
WITH CTE_FullYear AS
(
SELECT TOP (366) DATEADD(dd,[number],@YearStart) dates
FROM master.dbo.spt_values
WHERE [type] = 'P' AND [number] >= 0 AND [number] < 367 -- account for leap years
AND DATEADD(dd,[number],@YearStart) <= @YearEnd
ORDER BY number
)
SELECT dates
FROM
(
SELECT
dates
,[Month] = MONTH(dates)
,RID = ROW_NUMBER() OVER (PARTITION BY MONTH(dates) ORDER BY dates)
,WeekNo = DATENAME(week,dates)
,WeekOfMonth = DATEDIFF(wk,DATEADD(mm,DATEDIFF(mm,0,dates),0),dates)+1
FROM CTE_FullYear
WHERE DATEPART(dw,dates) = 6 -- select saturdays
) tmp
WHERE WeekOfMonth IN (2,4);
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2013 at 2:06 pm
I suggest this method:
DECLARE @date datetime
--@date is used just to make it easier to check other dates:
-- naturally you can hard-code GETDATE() in place of @date if you prefer
SET @date = GETDATE()
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, CAST(YEAR(@date) AS char(4)) + '0107') / 7 * 7 + 7, 5) AS Second_Saturday_Of_Year,
DATEADD(DAY, DATEDIFF(DAY, 5, CAST(YEAR(@date) AS char(4)) + '0107') / 7 * 7 + 21, 5) AS Fourth_Saturday_Of_Year
Edit: No code change, just wanted to add that the method above works for any/all date settings (DATEFIRST, etc.).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 15, 2013 at 10:52 pm
Thanx for your valuable reply.........
I got my Solution
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply