-->

Transact SQL - nth day of Month (DateName)

maqk® by Unknown | 3:10 AM

One of my projects wants me to identify the given date's day name (DateName) ad find its occurrence in the given month. Say today is Tuesday, 20th May 2014, and this is the 3rd Tuesday of this month.

Once it is identified that the given date [20 may 2014] is 3rd Tuesday of the month, go back to last month's 3rd Tuesday, print sales data, keep going back for last 4 months and print sales data, interesting :) yet tiring too

So we are going to know if today, or any given date's day (DateName) is 2nd Monday of the Month, or 4th Tuesday  ? or 3rd Sunday ? or nth day of Month

Just now I wrote some code to find the occurrence of day in current month. The code is not refactored for any good :D (Sorry its mid mid night and my brain cells are too short i guess). Any ways it does the trick

Declare @du_date datetime, @du_date_FirstDate datetime
Declare @du_date_DateName nvarchar(10), @thDayOccStr nvarchar(4)
Declare @thDayOcc int

Set @du_date = getDate()

Select @du_date_DateName = DATENAME(dw, @du_date)

Set @thDayOcc = 0
Set @du_date_FirstDate = dateadd(day,1-datepart(day,@du_date),@du_date)

While (@du_date_FirstDate <= @du_date)
Begin
--** Calculate which th day it is of this month, next we will find same for last month(s) **--
if (DATENAME(dw, @du_date_FirstDate) = @du_date_DateName)
Begin
Set @thDayOcc += 1
--** FOR LOGGIN , U MAY ENABLE FOLLOWING **--
--Select 'Adding', DATENAME(dw, @du_date_FirstDate_Copy), @du_date_DateName, @thDayOcc
End

Select @du_date_FirstDate = DateAdd(dd,1,@du_date_FirstDate)
End
Set @thDayOccStr = Convert(nvarchar(100), @thDayOcc)

Select [Date processed] = @DU_Date, @du_date_DateName, [DateName occurence in Month] = @thDayOcc,
 @thDayOccStr +
Case
When @thDayOcc = 1 Then 'st '
When @thDayOcc = 2 Then 'nd '
When @thDayOcc = 3 Then 'rd '
Else 'th '
End
+ @du_date_DateName + ' of the Month'

0 comments:

Post a Comment

top