November 1, 2019 at 6:26 pm
drew.allen wrote:I think it's a circular argument. You don't use it, because it's not familiar, but it's not familiar, because you don't use it. I've been using the -1 variant for years, and I never have an issue with confusing the two.
Drew
Maybe you don't, and maybe if you stick to just using that style for day addition it works. But for someone new you're teaching them that they can do other kinds of date manipulation as well with getdate() + .... and that can get much uglier very quickly.
I think you're confused about what we're discussing. I'm not talking about getdate()-1, I'm talking about using -1 instead of 0 in the code pattern DATEADD(MONTH, DATEDIFF(MONTH, -1, <reference date>), -1). I think that Scott is boxing himself into a corner by restricting it to just 0. And by only using that pattern, novices might not know how to update the pattern to handle overflows when they use a small time period, especially if it's not obvious that 0 is standing in for 1900-01-01.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2019 at 6:45 pm
This reminds me of a certain well known personality that denounces the use of engine-proprietary functionality because someone somewhere someday may have to use the code in another RDBMS and that proprietary code won't migrate to a different RDBMS. That's like forbidding the use of a scientific calculator at work because your cube-mate might only know how to use a 4 function calculator.
Writing code based on the possibility of novices viewing it in the future makes absolutely no sense to me. It's like saying that you have to use a While Loop because novices have no clue what a Tally Table or Function is.
If you don't believe that the use of -1 (as Drew did) is supportable, that's fine but you're dooming novices to always being novices. To be honest, if someone doesn't know what the -1 that I used in such code actually is, I don't want them touching my code because they're not qualified to touch it even if I used nested date functions with 0.
I also state that if someone writes code the produces an incorrect answer because of the use of -1 and that code makes it to production, then your entire SDLC is broken and needs to be fixed whether you continue to use -1 or not.
I use -1 and I also use a Tally Table, a Tally Function, and a bunch of other things that are not documented in Books Online (and sometimes not documented anywhere on the internet) and are generally not taught at advanced levels never mind novice levels. I flat out refuse to code to the lowest common denominator because it's stupid to not take advantage of every bit of functionality possible no matter what the language being used is... especially SQL.
I DO, however, use comments to teach what the code does. If a novice still can't figure it out, they still shouldn't be touching the code. If someone with intermediate or advanced knowledge can't figure it out with the comments I include, then they picked the wrong profession and should choose another.
For example...
SELECT GETDATE() + .3456) -- Add 08:17:39.840 (hh:mi:ss.mmm) expected build time to the current date and time as a fractional day.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2019 at 7:13 pm
I also agree with Drew and Jeff - but will also add that using -1 is actually more standard (in my opinion) than using an additional dateadd in the function:
Declare @startDate datetime = getdate();
Select fom = dateadd(month, datediff(month, 0, @startDate) + n.Number, 0)
, eom = dateadd(month, datediff(month, -1, @startdate) + n.Number, -1)
, eom2 = dateadd(day, -1, dateadd(month, datediff(month, 0, @startdate) + (n.Number + 1), 0))
From (Values (-1), (0), (1)) As n(Number)
Using the same pattern with 0 or -1 is easily documented and explained...plus it leads right into other uses of the pattern:
Declare @startDate datetime = getdate();
With monthDates
As (
Select fom = dateadd(month, datediff(month, 0, @startDate) + n.Number, 0)
, eom = dateadd(month, datediff(month, -1, @startdate) + n.Number, -1)
From (Values (-1), (0), (1)) As n(Number)
)
Select *
, StartDate = dateadd(day, -datediff(day, d.DayNumber, md.fom) % 7, md.fom)
, EndDate = dateadd(day, -datediff(day, -1 + d.DayNumber, md.eom) % 7, md.eom)
, StartDay = datename(weekday, dateadd(day, -datediff(day, d.DayNumber, md.fom) % 7, md.fom))
, EndDay = datename(weekday, dateadd(day, -datediff(day, -1 + d.DayNumber, md.eom) % 7, md.eom))
From monthDates md
Cross Apply (Values (0),(1),(2),(3),(4),(5),(6)) As d(DayNumber);
Here - the same pattern is used...but this now leads to many other uses...
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 1, 2019 at 7:28 pm
ZZartin wrote:drew.allen wrote:I think it's a circular argument. You don't use it, because it's not familiar, but it's not familiar, because you don't use it. I've been using the -1 variant for years, and I never have an issue with confusing the two.
Drew
Maybe you don't, and maybe if you stick to just using that style for day addition it works. But for someone new you're teaching them that they can do other kinds of date manipulation as well with getdate() + .... and that can get much uglier very quickly.
I think you're confused about what we're discussing. I'm not talking about
getdate()-1, I'm talking about using -1 instead of 0 in the code patternDATEADD(MONTH, DATEDIFF(MONTH, -1, <reference date>), -1). I think that Scott is boxing himself into a corner by restricting it to just 0. And by only using that pattern, novices might not know how to update the pattern to handle overflows when they use a small time period, especially if it's not obvious that 0 is standing in for 1900-01-01.Drew
Yeah you're right I thought I saw a getdate() -1 in there.
But to really be obvious instead of using 0 or -1 you would really use a date '19000101' is only 9 more characters 😛
November 1, 2019 at 8:21 pm
Nothing to do with full-blown "novices". I would expect people with reasonable experience in SQL Server to know what 0 is, it's the default "base date" in SQL Server. If not, they can quickly Google it and get an accurate answer, and why it's used. For -1, no.
Also, as noted before, -1 has caused errors because of out-of-order date calcs. To me, it's just not worth the potential issues. Not to say you can't use it, of course, but I'll avoid it.
I also won't use comments to explain the basic functioning of the code / code lines. As in the classically terrible code:
SET @counter = @counter + 1 --add 1 to count of files processed
[To be clear, the code should be more like: SET @processed_file_count = @processed_file_count + 1]
I say save the comments for higher, broader meaning, not basic code that should be self-documenting when possible.
Furthermore, I don't really trust code-level comments when I look at code, esp. 3-month old or older comments. They're often out of date and/or otherwise incorrect.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 1, 2019 at 9:27 pm
Also, as noted before, -1 has caused errors because of out-of-order date calcs. To me, it's just not worth the potential issues. Not to say you can't use it, of course, but I'll avoid it.
Not just out of order calcs, but something like a simple typo can introduce some weird bugs that aren't super obvious. For example
dateadd(month, datediff(month, -1, @startdate) + n.Number, -2)
Actually will get you the last day of the month, some of the time(and wrong if you actually wanted the second to last day of the month some times).... Where as,
DATEADD(day, - 2, dateadd(month, datediff(month, 0, @startdate) + n.Number, 0))
Will always be wrong(or right).
November 2, 2019 at 7:12 pm
The cool part about threads like this is that no one is actually incorrect. There are pros and cons on both sides and it might even be appropriate to use both takes on the code within the same object.
As with all else in SQL Server, "It Depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2019 at 8:56 pm
I guess I don't understand why anyone would write software based on the knowledge of novices. If it's important for a novice to be able to maintain something a bit esoteric or complex, write a comment. Better yet, teach the novices so they're not novices anymore but still write the comment!
That way I'm not the only one that can maintain the code. I've got enough work on my plate already(as I'm sure we all do). I don't have the time to sit and explain this(or other more complicated logic) to someone who's of lessor knowledge. Or have the maintenance of this code "always" having to be maintained by me. In the past I've tried to sit with others and explain the code, only to get behind on my work. This then required me to have to put in extra time to get caught up on my work. After years of this I decided it's simpler just to keep the code simple. I couldn't decide who was hired in the past and still can't, so, I keep it simple.
Unless someone can show me a huge time savings I will get with using the 0 or -1 method. I'll just stick with the simple DATEADD.
I understand the logic of the 0 or -1 method. But to me it just seems odd to be always trying to find the DATEDIFF in months from a given date and 01/01/1900. Then adding those number of months back to zero(01/01/1900) just to get the first date of the month.
DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0) AS Eleven01,
DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate) AS BeginMonthDate
To me the second line is simpler and easier to follow. Seems like it would be less work, but I'm sure someone out there can prove me wrong.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
November 4, 2019 at 9:11 pm
Jeff Moden wrote:I guess I don't understand why anyone would write software based on the knowledge of novices. If it's important for a novice to be able to maintain something a bit esoteric or complex, write a comment. Better yet, teach the novices so they're not novices anymore but still write the comment!
That way I'm not the only one that can maintain the code. I've got enough work on my plate already(as I'm sure we all do). I don't have the time to sit and explain this(or other more complicated logic) to someone who's of lessor knowledge. Or have the maintenance of this code "always" having to be maintained by me. In the past I've tried to sit with others and explain the code, only to get behind on my work. This then required me to have to put in extra time to get caught up on my work. After years of this I decided it's simpler just to keep the code simple. I couldn't decide who was hired in the past and still can't, so, I keep it simple.
Unless someone can show me a huge time savings I will get with using the 0 or -1 method. I'll just stick with the simple DATEADD.
I understand the logic of the 0 or -1 method. But to me it just seems odd to be always trying to find the DATEDIFF in months from a given date and 01/01/1900. Then adding those number of months back to zero(01/01/1900) just to get the first date of the month.
DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0) AS Eleven01,
DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate) AS BeginMonthDateTo me the second line is simpler and easier to follow. Seems like it would be less work, but I'm sure someone out there can prove me wrong.
Quickly, adapt your version to get an even year or an even hour. It's super easy with the standard code: literally just change MONTH to YEAR or HOUR, that's it. That's a main reason to stick with the standard pattern: it can be used for nearly all intervals:
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @CurrentDate), 0)
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, @CurrentDate), 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 4, 2019 at 9:16 pm
Jeff Moden wrote:I guess I don't understand why anyone would write software based on the knowledge of novices. If it's important for a novice to be able to maintain something a bit esoteric or complex, write a comment. Better yet, teach the novices so they're not novices anymore but still write the comment!
That way I'm not the only one that can maintain the code. I've got enough work on my plate already(as I'm sure we all do). I don't have the time to sit and explain this(or other more complicated logic) to someone who's of lessor knowledge. Or have the maintenance of this code "always" having to be maintained by me. In the past I've tried to sit with others and explain the code, only to get behind on my work. This then required me to have to put in extra time to get caught up on my work. After years of this I decided it's simpler just to keep the code simple. I couldn't decide who was hired in the past and still can't, so, I keep it simple.
Unless someone can show me a huge time savings I will get with using the 0 or -1 method. I'll just stick with the simple DATEADD.
I understand the logic of the 0 or -1 method. But to me it just seems odd to be always trying to find the DATEDIFF in months from a given date and 01/01/1900. Then adding those number of months back to zero(01/01/1900) just to get the first date of the month.
DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0) AS Eleven01,
DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate) AS BeginMonthDateTo me the second line is simpler and easier to follow. Seems like it would be less work, but I'm sure someone out there can prove me wrong.
Heh... first allow me to express how bad I feel for you if you're the only one in the company that can figure out the DATEADD/DATEDIFF method. And, no... I'm not being sarcastic in any way, shape, or form... been there... done that... don't ever wanna do it again. Seriously... if it's that bad, I wouldn't trust them with either formula.
I actually won't let people use the second method you posted because it's not bullet proof. The name of the column you're returning is "BeginMonthDate" but it'll only return just the date if @CurrentDate either has a "zero time" or is a datatype that won't carry a time. Since people don't actually know what they're doing, they could copy the code to a place where it would actually be a problem or someone could change the data or the datatype/data of wherever the code is currently being used.
But, to each their own. You know the people and the code you have to work with much better than I do.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2019 at 9:38 pm
below86 wrote:Jeff Moden wrote:I guess I don't understand why anyone would write software based on the knowledge of novices. If it's important for a novice to be able to maintain something a bit esoteric or complex, write a comment. Better yet, teach the novices so they're not novices anymore but still write the comment!
That way I'm not the only one that can maintain the code. I've got enough work on my plate already(as I'm sure we all do). I don't have the time to sit and explain this(or other more complicated logic) to someone who's of lessor knowledge. Or have the maintenance of this code "always" having to be maintained by me. In the past I've tried to sit with others and explain the code, only to get behind on my work. This then required me to have to put in extra time to get caught up on my work. After years of this I decided it's simpler just to keep the code simple. I couldn't decide who was hired in the past and still can't, so, I keep it simple.
Unless someone can show me a huge time savings I will get with using the 0 or -1 method. I'll just stick with the simple DATEADD.
I understand the logic of the 0 or -1 method. But to me it just seems odd to be always trying to find the DATEDIFF in months from a given date and 01/01/1900. Then adding those number of months back to zero(01/01/1900) just to get the first date of the month.
DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0) AS Eleven01,
DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate) AS BeginMonthDateTo me the second line is simpler and easier to follow. Seems like it would be less work, but I'm sure someone out there can prove me wrong.
Heh... first allow me to express how bad I feel for you if you're the only one in the company that can figure out the DATEADD/DATEDIFF method. And, no... I'm not being sarcastic in any way, shape, or form... been there... done that... don't ever wanna do it again. Seriously... if it's that bad, I wouldn't trust them with either formula.
I actually won't let people use the second method you posted because it's not bullet proof. The name of the column you're returning is "BeginMonthDate" but it'll only return just the date if @CurrentDate either has a "zero time" or is a datatype that won't carry a time. Since people don't actually know what they're doing, they could copy the code to a place where it would actually be a problem or someone could change the data or the datatype/data of wherever the code is currently being used.
But, to each their own. You know the people and the code you have to work with much better than I do.
The time portion has bit me and others at times throughout my career, most of the dates are defined as just date and not datetime.
There have been some real 'winners' I've had to work with(that's why I don't use my name here). This isn't the first, probably won't be the last time I say negative things about them on this site.
Simply CAST will fix the time portion. Bullet Proof!(fingers crossed)
CAST(DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate) AS DATE) AS BeginMonthDate
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
November 4, 2019 at 11:22 pm
Also, as noted before, -1 has caused errors because of out-of-order date calcs. To me, it's just not worth the potential issues. Not to say you can't use it, of course, but I'll avoid it.
Not just out of order calcs, but something like a simple typo can introduce some weird bugs that aren't super obvious. For example
dateadd(month, datediff(month, -1, @startdate) + n.Number, -2)Actually will get you the last day of the month, some of the time(and wrong if you actually wanted the second to last day of the month some times).... Where as,
DATEADD(day, - 2, dateadd(month, datediff(month, 0, @startdate) + n.Number, 0))Will always be wrong(or right).
This is an argument for using actual dates rather than integers that represent dates. In this formula what is being preserved is the absolute day of the month (when possible) not the relative day of the month. The absolute day is the 30th, because -2 represent 1899-12-30 rather than -2 days relative to the beginning of the month (i.e., the penultimate day of the month). When adding months to 1899-12-30, it will retain the 30 in months with 30 or 31 days, but will map to 28 (or more rarely 29) for February. The only reason that -1 works is because it represents 1899-12-31 and there are never more than 31 days in a month, so it will always map to the last day of the month. This is also the reason that I don't recommend using 58 (1900-02-28) even though it also represents the last day of the month. I don't want to preserve the 28th day of the month, I want to preserve the last day of the month. (-1 is also easier to remember.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 4, 2019 at 11:30 pm
Agreed on both the addition you made for the bullet proofing and, considering the situation, going incognito. Heh... I dare not go incognito... it would remove some self-imposed limits and I'd likely get black balled from the site. I could come on as a new user to the site but, eventually, people who do so are found out.
Your restraint is admirable.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply