How show only first friday of every month
-
Gotta say this, since it's a pet peeve of mine = Using comments in place of accurate names.
Viz, why?:
FROM Tally t --Month count sequence starting at 0
rather than:
FROM Tally month_count --or t_month or month#, etc.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
I am always scared when I see code I don't understand. I will offer a solution that requires very little coding, once preparations are done. Later, date based calculations are much simpler and do not require date functions. Of course, solution can be a calendar table. It is basically nothing new, calendar tables have been around since somebody figured out the idea of tables of numbers. Numbers are easy to convert to dates, then we extract attributes of dates into separate columns. We do it once and than live happily ever after. I know, from time to time we need to add something we did not think of initially, but that does not happen every day.
Thus, if we have a permanent calendar table, or a function, then the task is much simpler.
Here is how Calendar table could be created. There are many was to do this, even without SQL. Jeff's fnTally comes handy here.
CREATE OR ALTER FUNCTION dbo.MyCalendar()
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WiTH Dates AS
(
SELECT N, dateadd (d,N-1,'19000218' )AS TheDate
FROM dbo.fnTally (1,60000)
)
SELECT N
, Abs_Week = 1+N/8
, TheDate
, DofW_Number = row_number() OVER (PARTITION BY 1+N/8 ORDER BY N)
, Day_Name = datename(dw,TheDate)
, Month_Name = datename(mm,TheDate)
, Day_in_Month = datename(day,TheDate)
, Year_Num = datename(yy,TheDate)
, Month_Num = month(TheDAte)
, DWSeq = row_number()
OVER (PARTITION BY datename(yy,TheDate), month(TheDAte), datename(dw,TheDate)
ORDER BY datename(day,TheDate))
FROM Dates
GOWe do it once, add columns that would be useful in the future and let it sit in database. Then we can write things like this:
DECLARE
@Day_name varchar(10) --- name of the day, like (Monday,Tuesday,wednesday, thursday,.Friday,Saturday, Sunday)
, @From_year int
, @to_Year int
, @nthAppearence int -- which appearence of the day we want, 1st, 2nd, 3d, 4th, 5th
SELECT @Day_name = 'Sunday',@nthAppearence=5, @From_year=1960, @to_Year=2022
;
SELECT TheDate,Day_name, Month_name,Day_in_month, dwSeq
FROM dbo.MyCalendar()
WHERE Day_Name = @Day_name
AND DWSeq = @nthAppearence --3 -- every third Friday in a month
AND Year_num >=@From_year AND Year_Num<=@to_Year
ORDER BY year_num,month_num,nAdteAdd/DateDiff? Just add or subtract values in column N for given dates, you get the idea. Date range is from cca. 1900 till 2067 or so. By that time, most of us will be retired iw we are still around. So the solutions using calendar table are kind of permanent.
Just an idea, with using fnTally to generate sequence of numbers we need.
The table does not have to be created by any kind of SQL code. Excel does excellent job.
Zidar's Theorem: The best code is no code at all...
-
@JeffModen - I rather like Peter's version to get the Nth Weekday outlined here: https://weblogs.sqlteam.com/peterl/2009/06/17/how-to-get-the-nth-weekday-of-a-month/
And we had a discussion about my changes to his function here: https://qa.sqlservercentral.com/forums/topic/get-particular-date-from-a-month/page/3
I would think using that function with a simple inline-tally to generate a date in each month (doesn't matter what day) would work quite well. Note: the function works for all valid datetime dates and is not limited to 1900-01-01 and forward.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
- How many times have you ever worked with anything that required an output of storage of any date earlier than 1900? I can see people taking exception to that, though. I should probably make that tweak.
- Add a cCTE to what you're talking about and let's race. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Steve Collins wrote:
In the tvf it seems possible to factor out a common table expression and the UNION ALL by adding +1 to the row goal and -1 to the sequence accessor.
Good idea but I'm hoping that you don't think that I didn't know that. 😀 It doesn't matter as much as it did before but there used to be a substantial advantage to NOT doing all those integer math calculations.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Zidar wrote:
I am always scared when I see code I don't understand.
Thanks for the feedback and the code. It's always appreciated, especially when it's a slightly different take on things.
In reference to the quote above, I do the opposite. I use it as an opportunity to do some self-training.
On that note, do you think that most people will understand that 1+N/8 is a bit of a Mod 7 for unit based sequences ? You're only the second other person that I've ever seen use it in about 4 decades of programming. To be sure, I'm NOT saying to not use it. It's handy and easy but most folks won't know about it unless they've studied it before. It's just funny to me about how you post about code that scares you and then you use something a lot of folks have never seen before. 😀
Shifting gears a bit, why did you use 19000218 as a base date for your function? Why not start at the "0" date of 1900 (which a whole lot of people use)? The world gets a lot easier with that (one example will be that N will be the actual DateSerial number for the date) and so do some of your formulas not to mention the more traditional N/7+1 and N%7+1 (instead of having to do a ROW_NUMBER()).
I'll also point out that if you're only going to calculate only the first Friday of a month precisely 1 time, there's really no issue (except when people that know see it 😀 ) but if you have to do such a thing more than once (which other people may have to), then internally generating 60,000 rows for every use could become a pretty big problem (which means you should demonstrate how you'd use the MyCalendar table to do it, especially when it comes to indexing). If code like some of that written on this tread scares you, use it as a learning opportunity and hammer out how and why it works because the techniques folks are using are used a lot in other areas and a calendar table might not always be available or allowed.
Again, thanks for the code and "Welcome aboard"!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden wrote:
Good idea but I'm hoping that you don't think that I didn't know that. 😀 It doesn't matter as much as it did before but there used to be a substantial advantage to NOT doing all those integer math calculations.
Well I only know some of what you know. 🙂 I also don't know what you don't know. Would I guess that you know this? Yes. Does it mean an oversight in the code is impossible? Ha. At one point I recall an exchange where I asked about the handling of 0/1 in fnTally and iirc you pointed out it was to avoid +1/-1 when handling dates. Nicely answered btw. Because the fnTally 0/1 handling code is conditional and the alternative calculation (UNION ALL'ing 1 additional row) is not necessary (in the CTE) it seemed possible the code could be refactored.
I like Zidar's approach. In this thread and another their answer(s) relied on physical allocation(s) to simplify the final query. Nicely explained and with a working example in code
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
On the "hoping" thing, that was just a bit of "before I had enough coffee" conversation. 😀
On Zidar's approach, and like I said in my response, the method that he used in his post to solve the solution for this thread is fine if it's a one-off. If you have to use it on a regular basis, it could cause some issues but will always use to0 much CPU and generate too many internal rows for what is needed. I'd do like the calendar table approach he suggested and that's also I asked him to demo his suggested approach with a bit of code that actually does use a Calendar table and paying particular attention to indexing.
Anyway, thanks for the feedback, Steve, and good "seeing" you again, especially on such a popular subject.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
@jeff (#3932950)
Thank you for welcoming words. Thank you for very thorough reading of the post, means a lot to me. 🙂
I agree with all observations about oddities in the code. I will try to explain some of them.
The intention was to show solution by using calendar table, persistent in database. I needed a calendar table local to this post, so I used your fnTally for quick and dirty yet usable calendar table. By no means would calendar table be generated on the fly each time date calculations are needed. Something like this "if you have a calendar table, this is how you can do it too. You don't have a calendar table? No worries, here is one, we can use it in this occasion.."
On that note, do you think that most people will understand that 1+N/8 is a bit of a Mod 7 for unit based sequences ? You're only the second other person that I've ever seen use it in about 4 decades of programming.
I did not even think, but yes, again you are right, not too many people would understand it. That is because of my background - engineer graduated in Eastern Europe, well, 4 decades ago, age of Sinclair and Commodore 64 computers, when we were providing input data for work to key puncher ladies if we wanted to run our Fortran 4 programs. You know how it was back then. Math was serious subject, 4 semesters, mandatory for engineering studies.
Shifting gears a bit, why did you use 19000218 as a base date for your function? Why not start at the "0" date of 1900 (which a whole lot of people use)?
I know, my personal calendar table begins on 19000101. I wanted the week to start on Monday. The start date I choose was simply the nearest Monday I saw. That week I declared the first absolute week. Absolute week is is slightly different from representation of week of time in T SQL. It starts from 1 at some week in the past and for each week later the number goes up by 1. It does not start count from 1 in January next year. That is why it is 'Absolute'. Once I had a task of providing a report every week, no breaks for the end of the year so I came with absolute week. ISO Week is 'week in given year' where the week that begins in December and ends in January is counted either extra week in December, or first week of January. Now, if you point to me that there is actually Week function in TSQL equivalent to Absolute Week, I will be grateful for learning it, and ashamed 'cause I did not know it. Just joking, I am not ashamed when I do not know something and somebody shows me how to do it.
In the past, I was a moderator on SQL forum on a former Yugoslavian site. My nick was "Zidar" - means mason or bricklayer of old times. My signature was "Zidar's theorem: the best code is no code at all". I did not invent the expression, but it sounded nice and people liked it. I think I picked something like that on this site or the other one, Simple Talk. Most likely from you or J. Celko. And definitely influenced by Alex Kuznetsov, Itzik ben Gan, and Toon Koppelaars and late Lex deHaan.. I apologize for stealing the expression, but I just love the sound of it. 🙂
Cheers
Zidar-Bricklayer
Zidar's Theorem: The best code is no code at all...
-
I definitely have an appreciation for the "old" days. I used to be able to read the holes on a Hollerith card and tell you what it said. 😀
So, thanks for the nostalgic feedback but the question remains... Are you going to demonstrate how to solve the problem with a physical Calendar table or not? 😀 Te resurrect another bit of nostalgia, lemme say "Enquiring minds want to know"! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden wrote:
Add a cCTE to what you're talking about and let's race. 😉
I was wondering how a Dates table would compare to your function.
Here is some code to generate a dates table:
IF OBJECT_ID('dbo.DateTableTest','u') IS NOT NULL
DROP TABLE dbo.DateTableTest
GO
CREATE TABLE dbo.DateTableTest
(
DoMDate date not null,
[WeekFromStart] tinyint not null,
[WeekFromEnd] tinyint not null,
FirstDayOfMonth bit not null,
LastDayOfMonth bit not null,
DoW tinyint not null
);
INSERT INTO [dbo].[DateTableTest]
(
DoMDate,
[WeekFromStart],
[WeekFromEnd],
[FirstDayOfMonth],
[LastDayOfMonth],
[DoW]
)
select Value DoMDate,
COUNT(datename(weekday,value)) OVER (PARTITION BY datepart(yyyy,value),datepart(mm,value), datename(weekday,value) ORDER BY datename(weekday,value), value) [WeekFromStart],
COUNT(datename(weekday,value)) OVER (PARTITION BY datepart(yyyy,value),datepart(mm,value), datename(weekday,value) ORDER BY datename(weekday,value), value DESC) [WeekFromEnd],
CASE
WHEN Value = CONVERT(varchar(6),value,112) + '01' THEN 1
ELSE 0
END FirstDayOfMonth,
CASE
WHEN EOMONTH(Value) = Value THEN 1
ELSE 0
END LastDayOfMonth,
CASE datename(weekday,value)
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 7
ELSE 0
END DoW
--from dbo.DateRange('19000101','21001231','dd',1)
from dbo.fnTally(1,74000) t
cross apply(values (dateadd(dd,t.N,'19000101'))) Tally(Value)
order by 1
ALTER TABLE dbo.DateTableTest
ADD CONSTRAINT PK_DateTableTest PRIMARY KEY CLUSTERED (DomDate)
CREATE INDEX IX_DateTableTest_FirstDayOfMonth_DomDate ON dbo.DateTableTest(FirstDayOfMonth, DomDate);
CREATE INDEX IX_DateTableTest_LastDayOfMonth_DomDate ON dbo.DateTableTest(LastDayOfMonth, DomDate);
CREATE INDEX IX_DateTableTest_Dow_WeekFromStart_DomDate ON dbo.DateTableTest(Dow,WeekFromStart, DomDate);
CREATE INDEX IX_DateTableTest_Dow_WeekFromEnd_DomDate ON dbo.DateTableTest(Dow,WeekFromEnd, DomDate);Here are the equivalent queries to the examples in your function documentation
--===== Return the first day of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',0,1)
;
SELECT DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.FirstDayOfMonth = 1
--===== Return the 3rd Thursday of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',3,4)
;
SELECT DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.WeekFromStart = 3
AND dt.DoW = 4
--===== Return the last Tuesday of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',5,2)
;
SELECT DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.WeekFromEnd = 1
AND dt.DoW = 2
;
--===== Return the last day of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',6,1)
SELECT DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.LastDayOfMonth = 1 -
How to determine each 1st Friday in given time interval, using a permanent Calendar table.
A table must be created and populated with data in order to be permanent. I will create a temp table, but in practice non-temp table would be created. Table of numbers, virtual or real is required - I'll use fnTally in this case. Remember, it does not matter how you construct the Calendar . It does not matter how complicated or elegant way it was created. The one I use was completely developed in Excel, then cut/pasted into SQL table. How much time you spend on it also does not matter - you construct it once thne use it for years to come.
DROP TABLE IF EXISTS #Calendar
;
wiTH Dates AS
(
SELECT N, dateadd (d,N-1,'19000101' )AS TheDate
FROM dbo.fnTally (1,60000)
)
SELECT N
, Abs_Week = 1+N/8
, TheDate
, DofW_Number = row_number() OVER (PARTITION BY 1+N/8 ORDER BY N)
, Day_Name = datename(dw,TheDate)
, Month_Name = datename(mm,TheDate)
, Day_in_Month = datename(day,TheDate)
, Year_Num = datename(yy,TheDate)
, Month_Num = month(TheDAte)
INTO #Calendar
FROM Dates
;
ALTER TABLE #Calendar ADD CONSTRAINT Calendar_Uniq_N UNIQUE (N)
;
ALTER TABLE #Calendar ADD CONSTRAINT Calendar_Uniq_Date UNIQUE (TheDate)
;A few sample rows:
First and last of month are easy - pick the ones with earliest/latest date in a month for given day name:
---- First Friday in a month = the one with the earliest data within given month
SELECT Year_Num, Month_Num, MIN(Month_Name) AS [Month]
, MIN(Day_Name) AS [Day], MIN(TheDate) AS [Date of 1st Friday]
FROM #Calendar
WHERE 1=1
AND Day_name = 'FRIDAY'
--- 2014,2015 and 2016 year.
AND Year_num IN (2014, 2015,2016)
GROUP BY Year_Num, Month_Num, Day_Name
ORDER BY Year_Num, Month_Num, [Date of 1st Friday]
;
-- for the LAST occurance, use MAX(Date)Occurrences of given day name between first and last require a bit of work. For example, for each Friday in any month calculate ahead of time is it 1st, 2nd,3rd, 4th or 5th occurrence in all months in Calendar table, repeat for all days in a week. Then store that calculation as additional column in Calendar table. Too busy now to demonstrate a way to do it, perhaps a bit later. Again, it does matter how it is done, the final product is important.
Zidar's Theorem: The best code is no code at all...
-
@ Jonathan,
I modified your code and comments a bit to provide an easier to read output on SQL Profiler. I also made each snippet write to a throwaway variable to take display times out of the picture and I made it so each snippet would run 3 times.
Here's code with those modifications...
GO
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the first day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',0,1)
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the first day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.FirstDayOfMonth = 1
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the 3rd Thursday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',3,4)
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the 3rd Thursday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.WeekFromStart = 3
AND dt.DoW = 4
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the last Tuesday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',5,2)
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the last Tuesday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.WeekFromEnd = 1
AND dt.DoW = 2
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the last day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',6,1)
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the last day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
FROM dbo.DateTableTest dt
WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
AND dt.LastDayOfMonth = 1
GO 3
-----------------------------------------------------------------------------------------------------------------------
GOHere are the run results. Note that the Duration is in Micro-Seconds.
As we've found out in several articles and posts in the past, a properly indexed table (even just a raw Tally table) will frequently beat cCTE's and calculations and that's also the case here. The possible disadvantage is, as small as they are here, the reads.
There is a serious problem with the "equivalent" code you wrote though. It's not "equivalent" in the results for the last 3 tests. The table code comes up a month short.
I wonder if I can improve on the formulas in the function to erg out a little more speed.
Thanks for putting that together, Jonathan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
OK, here are more columns;
-- Add more columns:
-- Add more columns:
ALTER TABLE #Calendar
ADD Day_in_Year int -- 1,2,3...364,365 (366)
ALTER TABLE #Calendar
ADD DayOccuranceInMonth int -- 1,2,3,4 (5) for each Monday, Tuesday...Sunday
;Then we populate new columns:
-- populate new columns:
; WITH MyData AS
(
SELECT *
, Day_Count_in_Year = row_number() OVER (PARTITION BY Year_Num ORDER BY TheDate)
, Day_Occurance_In_Month = row_number() OVER (PARTITION BY Year_Num, Month_Num, DOfW_Number ORDER BY TheDate)
FROM #Calendar
--ORDER BY Year_num, Month_Num,TheDate
)
UPDATE MyData
SET Day_in_Year = Day_Count_in_Year
, DayOccuranceInMonth = Day_Occurance_In_Month
;so finally we can do this:
SELECT *
FROM #Calendar
WHERE Year_Num = 2021
AND Day_NAme = 'FRIDAY'
AND DayOccuranceInMonth IN ( 2,3)
ORDER BY Year_num, Month_Num,TheDate
;If you do not need dates from 1900 01 01, table can be cut at any point, say to start from 1 Jan 2010.
Adding column 'No_Work' may be useful, too.
Different people will have different version of Calendar table, or more than one if necessary. The principle of use is the same.
🙂
-- populate new columns:
; WITH MyData AS
(
SELECT *
, Day_Count_in_Year = row_number() OVER (PARTITION BY Year_Num ORDER BY TheDate)
, Day_Occurance_In_Month = row_number() OVER (PARTITION BY Year_Num, Month_Num, DOfW_Number ORDER BY TheDate)
FROM #Calendar
--ORDER BY Year_num, Month_Num,TheDate
)
UPDATE MyData
SET Day_in_Year = Day_Count_in_Year
, DayOccuranceInMonth = Day_Occurance_In_Month
;
Zidar's Theorem: The best code is no code at all...
-
Jeff Moden wrote:
There is a serious problem with the "equivalent" code you wrote though. It's not "equivalent" in the results for the last 3 tests. The table code comes up a month short.
Yes, I noticed the difference, I just thought I'd put it down to a feature of the code. It's an arguable point whether you'd want a date returned outside the start-end date range.
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply