CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Kevin
Kevin

Reputation: 338

How to select with a SQL query a date and for each 2 week pay period

How do I select 6/20/2014 and every pay date (every two weeks) before this date to the beginning of 2010? I also want to be able to select 6/20/2014 and every pay date (every 2 weeks) after this date to the end of 2020.

Thank you!

Upvotes: 0

Views: 1920

Answers (2)

Jerrad
Jerrad

Reputation: 5290

Create a Numbers table:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO NumbersTest
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)

Then

declare @startDate date = '6/20/2014'

--Dates from 1/1/2010 to 6/20/2014
select DATEADD(wk, -2 * (n.Number - 1), @startDate)
from NumbersTest n
where DATEADD(wk, -2 * (n.Number - 1), @startDate) >= '1/1/2010'

--Dates from 6/20/2014 to 12/31/2020
select DATEADD(wk, 2 * (n.Number - 1), @startDate)
from NumbersTest n
where DATEADD(wk, 2 * (n.Number - 1), @startDate) <= '12/31/2020'

SQL Fiddle

Upvotes: 1

Paul McLoughlin
Paul McLoughlin

Reputation: 2293

The SQL below can give you the answer to the first part of your question. Same principals can be used to get the answer to the second part.

declare @startDate as date = '20140620';

with myCte1 as
(
    select @startDate as dt
    union all
    select DATEADD(week, -2, dt)
    from myCte1
    where dt > '20100112'
)

select m.dt
from myCte1 as m
option (maxrecursion 1000)

Upvotes: 2

Related Questions