Divide a Year into different sections (datetime function, number function)




declare @groupNum int=5

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)


SELECT  dateadd(day,(365.25/@groupNum)*(num-1), dt) StartDate 
, CASE WHEN num=@groupNum Then  DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)  
Else dateadd(day,(365.25/@groupNum)*(num)-1 , dt) End EndDate 
from (select '1/1/2014' dt) m
cross apply (select n from Nums WHERE n<=@groupNum) d(num)

 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s