November 30, 2020 at 10:31 pm
Steve - if you set the test date to 2020-12-09 you should get 2020-12-10 as the Thursday following the second Tuesday of the month. At least that is how I read the OP's requirement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 30, 2020 at 10:44 pm
Just a little warning - you should not use a string literal in the function as that will be implicitly converted and could cause cardinality issues. Instead - you can use the integer value which will be natively converted to a datetime value.
It's too funny... People have jumped all over me for using "0" in a lot of the date functions as "being too obscure" and so I reluctantly make a change over time and, bang, I get nailed for the problem I was originally trying to avoid. I can't win.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 10:45 pm
It depends on what the "today" date is, no?
CASE 2 - If Today is 1 Dec 2020 - Then, The output should be (12 Nov 2020) -
Reason, 10 Dec 2020 (Thursday followed with 2nd Tue) is future date. 2nd Tue of Dec 2020 is 8th Dec 2020
If the 2nd Thursday of the test month is in the "future" then the output should be 12 Nov 2020.
Using 2020-12-09 example if today is 30 Nov then since the test date is in the future... the output should be 12 Nov 2020. Maybe the OP can shed light?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 30, 2020 at 10:54 pm
Jeffrey Williams wrote:Just a little warning - you should not use a string literal in the function as that will be implicitly converted and could cause cardinality issues. Instead - you can use the integer value which will be natively converted to a datetime value.
It's too funny... People have jumped all over me for using "0" in a lot of the date functions as "being too obscure" and so I reluctantly make a change over time and, bang, I get nailed for the problem I was originally trying to avoid. I can't win.
I hear you - this is something I found just recently...while testing for other date related functions. Microsoft really seems to have caused all kinds of issues with their date functions. For example - datediff wants to implicitly convert strings to datetimeoffset and will natively convert '0001-01-01 00:00:00.0000000' - but dateadd won't access that string because it cannot be converted to a datetime, but datediff will natively convert an integer to datetime.
It just doesn't appear to be consistent...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 30, 2020 at 10:59 pm
It depends on what the "today" date is, no?
CASE 2 - If Today is 1 Dec 2020 - Then, The output should be (12 Nov 2020) -
Reason, 10 Dec 2020 (Thursday followed with 2nd Tue) is future date. 2nd Tue of Dec 2020 is 8th Dec 2020
If the 2nd Thursday of the test month is in the "future" then the output should be 12 Nov 2020.
Using 2020-12-09 example if today is 30 Nov then since the test date is in the future... the output should be 12 Nov 2020. Maybe the OP can shed light?
Actually - it depends on what the OP expects...if the expected value is to always be less than the current date (test date) then we don't even need to look for the second Tuesday - just get the second Thursday of the month less than the current date.
Declare @testDate date = '2020-12-09';
Select Top 1
ThursdayFollowing = nwd.nthDate
From (Values (-1), (0)) As t(n)
Cross Apply dbo.fnGetNthWeekDay(dateadd(month, t.n, @testDate), 4, 2) nwd
Where nwd.nthDate <= @testDate
Order By
nwd.nthDate desc;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 30, 2020 at 11:01 pm
Oh wait I think I see what you're saying now. It should subtract 2 days from the current month 2nd Thursday to compare with the current date. The query could be like this
declare @test_dt date='2020-12-09',
@today date='2020-12-10';
select iif(dateadd(day, -2, t.thurs_dt)>@today, pr.thurs_dt, t.thurs_dt) answer_dt
from dbo.fn_test_scnd_thurs(@test_dt) t
cross join dbo.fn_test_scnd_thurs(eomonth(@test_dt, -1)) pr;
Output
answer_dt
2020-12-10
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 1, 2020 at 2:44 am
Post withdrawn... I need to stop testing at night.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2020 at 2:52 am
Here is another approach, looks a bit cleaner to me.
But that's, again, - to me.
Starting from obvious:
Declare @Today datetime
set @Today = GETDATE()
Finding the number of months since the "zero" date:
select DATEDIFF(mm, 0, @Today)
Finding the beginning of the current month:
select dateadd(mm, DATEDIFF(mm, 0, @Today), 0)
finding the beginning iof the previous month:
select dateadd(mm, DATEDIFF(mm, 0, @Today)-1, 0)
Now we need to figure out the dayNo for the beginning of the previous month and Today"
Declare @TodayNo int, @StartDateNo int
select @TodayNo = datediff(dd, 0, @Today),
@StartDateNo = datediff(dd, 0, dateadd(mm, DATEDIFF(mm, 0, @Today)-1, 0))
Now we generate the set of days between the beginning of the previous month and today:
select N, DATEADD(dd, N, 0) [date]
FROM Service.dbo.TallyGenerator (@StartDateNo, @TodayNo, null, 1)
-- The link to the function is in my signature
and pick only tuesdays from this set:
select DATEADD(dd, N, 0) Tue
FROM Service.dbo.TallyGenerator (@StartDateNo, @TodayNo, null, 1)
WHERE N%7 = 1
After that wenumbering those tuesdays within each month:
select ROW_NUMBER() over (partition by DATEDIFF(mm, 0, DATEADD(dd, N, 0)) order by N ) RN, DATEADD(dd, N, 0) Tue
FROM Service.dbo.TallyGenerator (@StartDateNo, @TodayNo, null, 1)
WHERE N%7 = 1
only thing left to do is to find latest Tue with RN = 2. 2 days later after that Tuesday will be the Thursday we're looking for:
SELECT top 1 Tue, Tue + 2 Thu
FROM (
select ROW_NUMBER() over (partition by DATEDIFF(mm, 0, DATEADD(dd, N, 0)) order by N ) RN, N, DATEADD(dd, N, 0) Tue
FROM Service.dbo.TallyGenerator (@StartDateNo, @TodayNo, null, 1)
WHERE N%7 = 1
) T1
WHERE RN = 2
order by Tue desc
Actually, I used variables only to simplify the code. Its not necessary:
SELECT top 1 Tue, Tue + 2 Thu
FROM (
select ROW_NUMBER() over (partition by DATEDIFF(mm, 0, DATEADD(dd, N, 0)) order by N ) RN, N, DATEADD(dd, N, 0) Tue
FROM Service.dbo.TallyGenerator (datediff(dd, 0, dateadd(mm, DATEDIFF(mm, 0, @Today)-1, 0)), datediff(dd, 0, @Today), null, 1)
WHERE N%7 = 1
) T1
WHERE RN = 2
order by Tue desc
If you have a calendar table (if you don't you should build it using the same tallyGenerator) it's pretty easy to test the solution against all the dates either from future or the past:
SELECT C.day_dt, Tue, Thu
FROM dbo.calendar C
CROSS APPLY (
SELECT top 1 Tue, Tue + 2 Thu
FROM (
select ROW_NUMBER() over (partition by DATEDIFF(mm, 0, DATEADD(dd, N, 0)) order by N ) RN, N, DATEADD(dd, N, 0) Tue
FROM Service.dbo.TallyGenerator (datediff(dd, 0, dateadd(mm, DATEDIFF(mm, 0, C.day_dt)-1, 0)), datediff(dd, 0, C.day_dt), null, 1)
WHERE N%7 = 1
) T1
WHERE RN = 2
order by Tue desc
) T2
ORDER BY day_dt
I tested it on SQL2008 - works fine.
_____________
Code for TallyGenerator
December 1, 2020 at 2:55 am
Jeff Moden wrote:Jeffrey Williams wrote:Just a little warning - you should not use a string literal in the function as that will be implicitly converted and could cause cardinality issues. Instead - you can use the integer value which will be natively converted to a datetime value.
It's too funny... People have jumped all over me for using "0" in a lot of the date functions as "being too obscure" and so I reluctantly make a change over time and, bang, I get nailed for the problem I was originally trying to avoid. I can't win.
I hear you - this is something I found just recently...while testing for other date related functions. Microsoft really seems to have caused all kinds of issues with their date functions. For example - datediff wants to implicitly convert strings to datetimeoffset and will natively convert '0001-01-01 00:00:00.0000000' - but dateadd won't access that string because it cannot be converted to a datetime, but datediff will natively convert an integer to datetime.
It just doesn't appear to be consistent...
That and simple things like you can't use date serial numbers when diffing or adding to a DATE or DATETIME2 column. Man... they really crippled those two datatypes compared to DATETIME.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2020 at 5:13 am
@Jeffrey Williams,
I tested your performance improvements for Peter's code and they're an order of magnitude faster. Nicely done. I've also not been able to find a fault in the output with the bit of testing I've done. This is really cool because of the functionality to work backwards in a month to find the "last" DoW occurrence in a month. Of course, that can also be done by finding the first occurrence in the next month and then simply subtracting 7 days but it's still handy. I don't know if it causes and performance loss in having that functionality because I've not tested for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2020 at 6:34 am
This works perfectly. A little change in the output. If I pass TestDate as Today. It has to get me Thursday (follows with 2nd Tue - 12 Nov 2020) of last month and Current month Thursday which follows with 2nd Tue (10 Dec 2020).
December 1, 2020 at 8:02 am
Look at the last script from my post.
It allows to the function against all the dates you can possibly get.
in between the brackets at CROSS APPLY (...) T2 you can put any function you choose to use.
The script retustns you Thursdays after 2nd Tuesdays for every given date. You may check it by yourself, and then approve the schedule with whoever is at charge. After this you can guarantee no nasty surprises in the future - you've seen it already.
_____________
Code for TallyGenerator
December 1, 2020 at 1:44 pm
Look at the last script from my post.
It allows to the function against all the dates you can possibly get.
in between the brackets at CROSS APPLY (...) T2 you can put any function you choose to use.
The script retustns you Thursdays after 2nd Tuesdays for every given date. You may check it by yourself, and then approve the schedule with whoever is at charge. After this you can guarantee no nasty surprises in the future - you've seen it already.
I'm definitely going to check it out... I just haven't gotten there, yet. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2020 at 3:40 pm
@Jeffrey Williams,
I tested your performance improvements for Peter's code and they're an order of magnitude faster. Nicely done. I've also not been able to find a fault in the output with the bit of testing I've done. This is really cool because of the functionality to work backwards in a month to find the "last" DoW occurrence in a month. Of course, that can also be done by finding the first occurrence in the next month and then simply subtracting 7 days but it's still handy. I don't know if it causes and performance loss in having that functionality because I've not tested for that.
Thank you - the functionality is all Peter's work though, and when it comes to date math in SQL Server I trust his work. I doubt there is any performance hit with using a negative value as @theNthDay - since it all comes down to simple integer math at that point.
I really didn't expect to see any performance improvement though...just restructured and removed the string dates. I can see the string dates improving performance a little...but didn't think it would be that much faster.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 1, 2020 at 3:44 pm
This works perfectly. A little change in the output. If I pass TestDate as Today. It has to get me Thursday (follows with 2nd Tue - 12 Nov 2020) of last month and Current month Thursday which follows with 2nd Tue (10 Dec 2020).
So - which solution did you utilize? And what changes in the output did you need to make?
It isn't clear what you are looking for...are you saying you need to return both dates when the test date is 2020-01-01?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply