Last Thursday of the month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Last Thursday of the month

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2015-05-06 : 09:05:54

Is there a better way to write the function?

Getting the Last Thursday of the month from the date given. If the last Thursday has passed then find the last Thursday of next month.

ALTER FUNCTION [dbo].[ufn_LastThursdayfromADate]
(
-- Add the parameters for the function here
@getDate Date
)
RETURNS Date
AS
BEGIN

DECLARE @LastThursdayfromADate DATE,
@FirstDateOftheMonth DATE,
@FirstDateOfNextMonth DATE;

SET @FirstDateOftheMonth = DATEADD(dd,-(DAY(@getDate)-1),@getDate);
SET @FirstDateOfNextMonth = DATEADD(dd,-(DAY(DATEADD(mm,1,@FirstDateOftheMonth))-1),DATEADD(mm,1,@FirstDateOftheMonth));

SELECT @LastThursdayfromADate = MAX(Thursdaydate)
FROM
(
select dateadd(day,number,@FirstDateOftheMonth) AS Thursdaydate FROM master..spt_values
where type='p' and number<32
) t
WHERE datename(weekday,Thursdaydate) = 'thursday'

IF(@LastThursdayfromADate <= @getDate)
BEGIN
SELECT @LastThursdayfromADate = MAX(Thursdaydate)
FROM
(
select dateadd(day,number,@FirstDateOfNextMonth) AS Thursdaydate FROM master..spt_values
where type='p' and number<32
) t
WHERE datename(weekday,Thursdaydate) = 'thursday'

END

RETURN @LastThursdayfromADate;

END

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 09:47:34

[code]
DECLARE @getDate Date = '20150101' -- A Test Value

DECLARE @LastThursdayfromADate date,
@FirstDateOfNextMonth date,
@intWeekday int

SELECT @FirstDateOfNextMonth = DATEADD(Month, DATEDIFF(Month, 0, @getDate)+1, 0),
@intWeekday = DATEPART(Weekday, @FirstDateOfNextMonth),
@LastThursdayfromADate = DATEADD(Day, 5 - @intWeekday - CASE WHEN @intWeekday <= 5 THEN 7 ELSE 0 END, @FirstDateOfNextMonth)

-- Display workings and result:
SELECT [@FirstDateOfNextMonth] = @FirstDateOfNextMonth,
[@intWeekday] = @intWeekday,
[@LastThursdayfromADate] = @LastThursdayfromADate,
[Weekday] = DATENAME(Weekday, @LastThursdayfromADate)
[/code]

I think "first day of week" is configurable, in SQL, so would have to watch out in case that is set to something non-standard

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 09:49:35

I expect there is a way to do this with CTE or APPLY to save having to have intermediate storage of @FirstDateOfNextMonth & @intWeekday (and to avoid having to specify them twice!)

I've seen solutions based on using a TALLY table, but it seems unnecessary to me to use a table lookup as this is just integer maths, which I assume?? is quicker.

mapidea
Posting Yak Master

124 Posts

Posted - 2015-05-06 : 11:09:03

Thank you for your reply.

You have used CASE statement to make it simple.

IF(@LastThursdayfromADate <= @getDate)
How can we get the Last Thursday from Next month?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-06 : 11:28:13

SET DATEFIRST affects the algorithm. Here's a version that takes that into account:


SELECT TestDate, Date1st, offset, FirstDateOfNextMonth, intWeekday, LastThursdayfromADate, datepart(weekday, LastThursdayfromADate) AS DayNumber, datename(weekday, LastThursdayfromADate) AS DayName
FROM (
	VALUES (
		@TestDate, 
                @@DATEFIRST, 

                -- compute the offset to Thursday for the current setting of DATEFIRST
                CASE 
			WHEN @@DATEFIRST > 5
				THEN @@DATEFIRST - 2
			ELSE 5 - @@DATEFIRST
			END
		)
	) _1(TestDate, Date1st, offset)

-- First day of next month
CROSS APPLY (
	SELECT DATEADD(Month, DATEDIFF(Month, 0, TestDate) + 1, 0)
	) _2(FirstDateOfNextMonth)

-- Weekday number of first day of next month
CROSS APPLY (
	SELECT DATEPART(Weekday, FirstDateOfNextMonth)
	) _3(intWeekday)

-- back up to last Thursday of preceding month
CROSS APPLY (
	SELECT DATEADD(Day, offset - intWeekday - CASE 
				WHEN intWeekday <= offset
					THEN 7
				ELSE 0
				END, FirstDateOfNextMonth)
	) _4(LastThursdayfromADate)
;

Note that all the APPLYs do not affect the final execution plan. The compiler inlines all the work

Gerald Britton, MCSA
Toronto PASS Chapter

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 13:53:30

quote:
Originally posted by mapidea

How can we get the Last Thursday from Next month?

You need to understand the code, rather than blindly copying it - otherwise you won't be ale to support it

Probably this (I haven't tested it)


@FirstDateOfNextMonth = DATEADD(Month, DATEDIFF(Month, 0, @getDate)+2, 0)

SELECT DATEADD(Month, DATEDIFF(Month, 0, TestDate) + 2, 0)

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-05-06 : 13:55:18

you could use the following which is datefirst agnostic

ALTER FUNCTION [dbo].[ufn_LastThursdayfromADate]
(@getDate Date)
RETURNS Date
AS
BEGIN
DECLARE @DTprimer datetime = DATEADD(dd,-DATEPART(dd,@getDate) +1,@getDate)
DECLARE @LastThursdayfromADate DATE

;With myCal
AS
(
SELECT @DTprimer D
UNION ALL
SELECT DATEADD(dd,1,D)
FROM myCal
WHERE D< DATEADD(mm,2,@DTprimer) - 1
)
SELECT @LastThursdayfromADate = MIN(D) FROM (SELECT MAX(D) D FROM myCal WHERE DATENAME(dw,D) = 'Thursday' GROUP BY MONTH(D)) D WHERE D >= @getDate -- determine what you want to do if you pass the past thursday
RETURN @LastThursdayfromADate;
END

-- test

DECLARE @TestDate date = '5/29/2015'

SELECT [dbo].[ufn_LastThursdayfromADate](@TestDate)

SET @TestDate = '5/27/2015'
SELECT [dbo].[ufn_LastThursdayfromADate](@TestDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources