Second Friday of every month
- Jeff Moden wrote:
Heh... Wecome aboard but... "Must Look Eye!"
You've posted what looks like Oracle code in an SQL Server forum and it's not going to work in SQL Server.
On a 6+ year old thread 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:Jeff Moden wrote:
Heh... Wecome aboard but... "Must Look Eye!"
You've posted what looks like Oracle code in an SQL Server forum and it's not going to work in SQL Server.
On a 6+ year old thread 😉
To be honest, I don't care how old the original post is. If someone sees something wrong or has a better idea, I don't care if the post is decades old. Post away.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Install the DateRange table valued function here: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
PRINT 'CREATE FUNCTION [dbo].[DateRange]'
EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X')
END
GO
/*-- **********************************************************************
-- FUNCTION: DateRange
-- Returns a table of datetime values based on the parameters
-- Parameters:
-- @StartDate :Start date of the series
-- @EndDate :End date of the series
-- @DatePart :The time unit for @interval
-- ns : nanoseconds
-- mcs : microseconds
-- ms : milliseconds
-- ss : seconds
-- mi : minutes
-- hh : hours
-- dd : days
-- ww : weeks
-- mm : months
-- qq : quarters
-- yy : years
-- @Interval :The number of dateparts between each value returned
--
-- Sample Calls:
-- SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
-- SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
-- SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default)
-- SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7)
-- SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
-- **********************************************************************/
ALTER FUNCTION [dbo].[DateRange]
(
@StartDate datetime2,
@EndDate datetime2,
@DatePart nvarchar(3)='dd',
@Interval int=1
)
RETURNS TABLE AS RETURN
WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart
WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
END) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM A A, A B, A C, A D, A E, A F, A G, A H) -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
SELECT CASE @DatePart
WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate)
WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate)
WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate)
WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate)
WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate)
WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate)
WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate)
WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate)
WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate)
WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate)
ELSE DATEADD(dd, T.AddAmount, @StartDate)
END [Value]
FROM B
CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount)
GOThen write a query like this:
DECLARE @StartDate varchar(20) = '2021-01-01'
;with cte as
(
select dr.value dt,
DATENAME(dw,dr.value) DAY,
DATENAME(mm,dr.value) MONTH,
YEAR(dr.value) YEAR,
ROW_NUMBER() OVER (PARTITION BY DATEPART(month,dr.Value) ORDER BY dr.Value) rn
from dbo.DateRange(@StartDate , DATEADD(yy,1,@StartDate),'dd',1) dr
where DATEPART(weekday,dr.Value)=6
)
select x.dt, x.DAY, x.MONTH, x.YEAR
from cte x
where rn=2Results:
-
Practically, methods like that work just fine. Technically, it bugs me because the "Tally" sequence generator had to build 366 values and then were filtered out to just 12 quite late in the game.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - lokesh97singh wrote:
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
3rd friday : (SELECT DATEADD(DAY, 14 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
Ignore following!
5th Business Day SQL : (SELECT DATEADD(DAY, 6 - DECODE(EXTRACT(DOW FROM START_DATE), 1, 2,0, 1, 0), START_DATE) FROM (SELECT date_trunc('month',current_date) START_DATE))
BTW... although that won't work in SQL Server, your use of DECODE and EXTRACT is also very cool. IIRC, this isn't for Oracle because I see SELECTs without "FROM DUAL" in a couple of places. Which database engine did you write this clever code for? It could help someone that gets here even though they may have not been looking for an SQL Server/T-SQL solution.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
Practically, methods like that work just fine. Technically, it bugs me because the "Tally" sequence generator had to build 366 values and then were filtered out to just 12 quite late in the game.
Yes, it's brute force, but it is very easy to edit the code to get any weeks occurrence of any day.
-
Peter Larrson created the function outlined here: https://weblogs.sqlteam.com/peterl/2009/06/17/how-to-get-the-nth-weekday-of-a-month/
I updated that function and showed it here: https://www.sqlservercentral.com/forums/topic/get-particular-date-from-a-month
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
This way is much more efficient than my previous effort to get the Nth occurrence of any weekday for an entire year:
declare @year varchar(20) = '2021'
declare @Weekday int = 6 -- Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7
declare @Nth int = 2 -- 1= 1st occuence, 2=2nd occurrence, 3=3rd occurrence of @Weekday
select @Year [@Year],
@Weekday [@Weekday],
@Nth [@Nth Occurrence],
w.NthWeekday [Date of @Nth @Weekday]
from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) t(n)
cross apply(values(dateadd(mm,t.n,convert(date,@year +'0101')))) u(FirstOfMonth)
cross apply(values(dateadd(dd,(((@Weekday-datepart(weekday,u.FirstOfMonth)) + 7) % 7 + (@Nth-1)*7),u.FirstOfMonth))) w(NthWeekday)Just set:
@Year to the year you want e.g. '2021' for this year
@Weekday to the required day, e.g. 6 for Friday
@nth to the occurrence you need e.g. 2
The SQL will then return the dates for all the 2nd occurrences of Friday for the year 2021:
-
This was removed by the editor as SPAM
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply