Get the date of every second day of the month

Question

Is there a way to find out the date of every second day of the month using tsql syntax? For instance. in March it is the 12th, in April - the 9th.

thanks and cheers alex

Answer 1

Here's how you can find all the "minor days" in 2013.

select dateadd(day, 8, datediff(day, 1, dateadd(month, n, '2013-01-07')) / 7 * 7) date from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) t(n) 

Answer 2

Not knowing what the actual inputs and outputs are actually required, all I can give you is currently a predicate for defining a date as the second day of my month:

 DATEPART(day,@Date) between 8 and 14 and --Find the second one in the month DATEPART(weekday,@Date) = DATEPART(weekday,'20130319') --Make sure its a Tuesday 

(I use a fixed, well-known Tuesday to not know that the DATEFIRST settings are in effect when the request works)

---

This finds the corresponding Tuesday for the current month, but obviously @Date can be set to any date of interest:

 declare @Date datetime set @Date = CURRENT_TIMESTAMP ;with Numbers as (select n from (values (0),(1),(2),(3),(4),(5),(6)) t(n)), PotentialDates as (select DATEADD(day,n,DATEADD(month,DATEDIFF(month,'20010101',@Date),'20010108')) as Date from Numbers ) select * from PotentialDates where DATEPART(weekday,Date) = DATEPART(weekday,'20130319') 

(And, hopefully, it is also clear that the query can be part of a larger query, where @Date was instead of the column value, and therefore it can become part of a set-based approach to all of the work)

Answer 3

This code will give you every 1st and 3rd Sunday of the month .. so SQL welcomes :-)

 declare @dt datetime select @dt = '12/01/2014' select dateadd(mm,datediff(mm,'',@dt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@dt),'')+0)+ 8 select dateadd(mm,datediff(mm,'',@dt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@dt),'')+0)+ 22