Sql to get the week of year , month of quarter , month of year and quarter of year

Declare

@dd datetime;

Declare
@cntr int;

Set
@cntr=0;

Select
@dd=DATEADD (dd ,@cntr , '2013-08-11' )




Select

 
datediff
(week, dateadd(year, datediff(year, 0, @dd),0), @dd) + 1 weekOfYearCnt,

case
when (datepart(month,@dd))%3=0

then
'3'

else


(

datepart(month,@dd))%3

End
as monthOfQuarterCnt,

datepart
(month,@dd) as monthOfYearCnt,

Cast
(YEAR ( @dd ) as Varchar(4)) +' Q'+Cast((datepart (month,@dd) -1)/3+1 as Varchar(2)) quarterNm,

(
datepart (month,@dd) -1)/3+1 quarterOfYearCnt



 

Sql to getthe week of a month and week of a quarter

Declare

@dd datetime;

Declare
@cntr int;

Set
@cntr=0;

Select
@dd=DATEADD (dd ,@cntr , '2013-08-11' )

Select
 
(
DAY( @dd) +(DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0, @dd), 0))-1)-1)/7 +1 as weekOfMonthCnt,

DATEDIFF
(WEEK, DATEADD(QUARTER, DATEDIFF(QUARTER, 0,@dd), 0), @dd) weekOfQuarterCnt



 


 

Sql to get day of quarter and day of year

 
Declare
@dd datetime;

Declare
@cntr int;

Set
@cntr=0;

Select
@dd=DATEADD (dd ,@cntr , '2013-08-11' )

 
Select
 
DATEDIFF(d, DATEADD(qq, DATEDIFF(qq, 0, @dd), 0), @dd) + 1 AS dayOfQuarterCnt

,
DATEPART(dy, @dd) AS dayOfYearCnt


 

Sql to get day of week and day of month

 

Declare
@dd datetime;

Set @dd= '2013-08-11'
 
Select
datepart(dw, @dd) as dayofweekCnt , datename(dw, @dd) As dayOfWeekNm,DAY(@dd) AS dayOfMonthCnt