Friday, May 15, 2015

Week of Month calculation in sql server

In SQL server you can't get the Week of Month calculation.

To archive the situation please go through given code.

Here GETDATE()='2015-05-15'

select datediff(week, dateadd(month, datediff(month, 0, GETDATE()), 0), GETDATE()) +1 AS  WEEK_OF_MONTH

In this formula e "Zero" indicates '1900-01-01'

OR



select datediff(week, dateadd(month, datediff(month, '1900-01-01', GETDATE()), '1900-01-01'), GETDATE()) +1 AS  WEEK_OF_MONTH

Step by step:


select datediff(month, 0, GETDATE())--1384 months (In sql server default start date is 1900-01-01)

select dateadd(month, 1384, 0)--2015-05-01

select datediff(week,'2015-05-01',GETDATE())+1 --Ans: 3


No comments:

Post a Comment