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.
| 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)
|
|
|
|
|
|
|
|
|
Last Thursday of the month