determining a day whether it is first,second,third,fourth or last in a month

Question

1.00/5 (1 vote)

See more:

Hi I am using sql server 2008.
The function datepart(wk,getdate()) gives the day of the week.
If it is 1, then it is Sunday.
I need to get whether it is first,second,third,fourth or last Sunday.
Thank you

Posted 24-Oct-14 2:39am

johnyroyan

Updated 26-Jun-21 7:37am

Add a Solution

3 solutions

Solution 1

Tons of examples in net. The one of them is: http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/[^]

[EDIT]

SQL

;WITH CTE AS
(
    SELECT CONVERT(DATE, '2014-10-01') AS MyDate
    UNION ALL
    SELECT DATEADD(DD, 1, MyDate) AS MyDate
    FROM CTE
    WHERE MyDate<GETDATE()-1
)
SELECT MyDate, DATENAME(dw,MyDate) AS TheNameOfTheDay, ROW_NUMBER() OVER(PARTITION BY DATENAME(dw,MyDate) ORDER BY MyDate) AS NoOfDayInMonth
FROM CTE
ORDER BY MyDate

Above example returns:

2014-10-01	Wednesday	1
2014-10-02	Thursday	1
2014-10-03	Friday		1
2014-10-04	Saturday	1
2014-10-05	Sunday		1
2014-10-06	Monday		1
2014-10-07	Tuesday		1
2014-10-08	Wednesday	2
2014-10-09	Thursday	2
2014-10-10	Friday		2
2014-10-11	Saturday	2
2014-10-12	Sunday		2
2014-10-13	Monday		2
2014-10-14	Tuesday		2
2014-10-15	Wednesday	3
2014-10-16	Thursday	3
2014-10-17	Friday		3
2014-10-18	Saturday	3
2014-10-19	Sunday		3
2014-10-20	Monday		3
2014-10-21	Tuesday		3
2014-10-22	Wednesday	4
2014-10-23	Thursday	4
2014-10-24	Friday		4

As you can see, the NoOfOfDayInMonth increases each week.

Posted 24-Oct-14 2:56am

Maciej Los

Updated 24-Oct-14 3:18am

v2

Comments

johnyroyan 24-Oct-14 9:07am

No. It simply gives:
Last Day of Previous Month
First Day of Current Month
Today
Last Day of Current Month
First Day of Next Month

What i need is that if today is Friday, then whether it is first, second, third,fourth
or last Friday.

Maciej Los 24-Oct-14 9:10am

OK, i'll improve my question.

Maciej Los 24-Oct-14 9:19am

Check now ;)

Solution 3

I know this post is old but here's another method for 2008 and up (Comment out any returned columns that you don't need).

--===== 2008 and Up
 SELECT  Occurance#   = (DAY(GETDATE())-1)/7+1
        ,Occurance    = CASE 
                        WHEN MONTH(GETDATE()) = MONTH(DATEADD(dd,7,GETDATE())) 
                        THEN v.OccuranceName 
                        ELSE 'LAST'
                        END
        ,DoW          = DATENAME(dw,GETDATE())
   FROM (VALUES (1,'1st'),(2,'2nd'),(3,'3rd'),(4,'4th'),(5,'5th'))v(Occurance#,OccuranceName)
  WHERE v.Occurance#  = (DAY(GETDATE())-1)/7+1
;

On my box and dumping the output to variables to remove display durations, it runs on a million dates in about 392ms.

Just as a bit of a sidebar (it's just a habit of mine to check), Phil's good code takes 654ms to do the same thing. I agree that's not much especially considering it's for a million rows but it does run 66% faster. Imagine if everything on your server ran "just" 66% faster. ;)

From reading the comments, I also know this request was for a 2008 instance but figured I'd give it a try using the wiles of 2012. It makes for some very short code, indeed, but EOM is a wee-bit slower than most brute-force methods and so the following took 408ms. The simplicity might be well worth the tiny loss, though.

--===== 2012 and Up
 SELECT  Occurance# = (DAY(GETDATE())-1)/7+1
        ,Occurance  = IIF(DATEDIFF(dd,GETDATE(),EOMONTH(GETDATE()))<7
                         ,'Last'
                         ,CHOOSE((DAY(GETDATE())-1)/7+1,'1st','2nd','3rd','4th'))
        ,DoW        = DATENAME(dw,GETDATE())
;

Posted 26-Jun-21 7:37am

Jeff Moden

Comments

Jeff Moden 29-Jun-21 10:09am

Instead of just looking at the code and coming to the wrong conclusion because you don't actually understand the code, run the code and see that it solves the OPs problem. ;)

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Accepted Answer · 2014-10-24T03:16:00

Solution 2

SELECT
CASE 
WHEN DAY(GETDATE()) BETWEEN 1 AND 7 THEN '1st'
WHEN DAY(GETDATE()) BETWEEN 8 AND 14 THEN '2nd'
WHEN DAY(GETDATE()) BETWEEN 15 AND 21 THEN '3rd'
WHEN DATEDIFF(day, 
	'1 ' + DATENAME(month,GETDATE()) + DATENAME(year,GETDATE()),
	DATEADD(month,1,
		'1 ' + DATENAME(month,GETDATE()) + DATENAME(year,GETDATE()))
	)-DAY(GETDATE())<7  THEN 'Last'
Else  '4th'
END

.

Posted 24-Oct-14 3:16am

PhilLenoir

Updated 24-Oct-14 5:09am

v5

Comments

johnyroyan 24-Oct-14 9:25am

Thank you for this logic. I still need to find out the logic for last Sunday

PhilLenoir 24-Oct-14 9:44am

Which version of SQL Server are you using. 12 has a function "EOMONTH" that can be used in the searched CASE.

johnyroyan 24-Oct-14 9:46am

I am using sql server 2008

PhilLenoir 24-Oct-14 9:47am

The easiest way to do this is a CLR function using DateTime.DaysInMonth. How are your .NET skills?

johnyroyan 24-Oct-14 9:53am

No.I need to calculate this in stored procedure itself, as this logic is a part of a fairly large stored procedure

PhilLenoir 24-Oct-14 9:54am

You can call a CLR function from a stored proc or any T-SQL! Is CLR enabled on your server?

johnyroyan 24-Oct-14 10:00am

I need to confirm this with DBA. Taking that CLR is enabled in my server, how to go about this using DateTime.DaysInMonth?

PhilLenoir 24-Oct-14 10:13am

Create your CLR function in VS using a "Database" template.

The DateTime.DaysInMonth function takes a year and month and returns the number of days, taking into account leap years. If the date of the Sunday (or any other day) is GREATER THEN the number of days in the month less 7 then it's the last of that day in the month.

PhilLenoir 24-Oct-14 10:20am

I've just thought of a simple way using T-SQL - see modified solution.

johnyroyan 24-Oct-14 10:32am

Thank you very much for this solution. And I thank you once again for giving information about using CLR function in sql server

PhilLenoir 24-Oct-14 10:41am

No problem. It must be Friday or I'd have thought of the T-SQL sooner!

PhilLenoir 24-Oct-14 11:08am

I spoke too soon. On thinking about it, there was a bug when the current day date is greater then the number of days in the next month. I've amended the solution to use the 1st if the month to calculate the number of days in the month.

My apologies - it really MUST be Friday!

determining a day whether it is first,second,third,fourth or last in a month

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

determining a day whether it is first,second,third,fourth or last in a month

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us