January 22, 2018 at 10:00 am
drew.allen - Monday, January 22, 2018 9:54 AMbelow86 - Monday, January 22, 2018 9:48 AMThis may be 'the long way around the barn', but it works. You could set this up a a SP and pass it the number of months you want and a start date(current date).
DECLARE @months AS INT;
DECLARE @startdate AS DATE;
DECLARE @Begindate AS DATE;DECLARE @datetable TABLE
(
Year_int INT,
Month_Int INT,
Month_Abv CHAR(3),
BOM_Date DATE,
EOM_Date DATE
)
;SET @months = 30;
SET @startdate = GETDATE();SET @Begindate = (SELECT DATEADD(dd, -(DATEPART(dd, @startdate) - 1), @startdate));
LOOPHERE:
INSERT INTO @datetable
SELECT YEAR(@Begindate) AS Year_int,
MONTH(@Begindate) AS Month_Int,
LEFT(CONVERT(CHAR(11), @Begindate, 107), 3) AS Month_Abv,
@Begindate AS BOM_Date,
DATEADD(dd, -1, DATEADD(MM, 1, @Begindate)) AS EOM_Date
;SET @months = @months -1;
SET @Begindate = (SELECT DATEADD(mm, -1, @Begindate));
IF @months > 0
GOTO LOOPHERE;
SELECT *
FROM @datetable
;A loop is horribly inefficient compared to the tally table method outlined earlier in this thread.
Drew
I understand it may not be as efficient, but based on the results needed for the original post I doubt you would notice a difference. I will take note of the tally table for future larger needs.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 22, 2018 at 10:58 am
In a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.
In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 22, 2018 at 11:04 am
below86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.
Understood but by would you intentionally want to write more complicated, less efficient code that takes longer (no matter how small that may be. (that wasn't actually a question... it was a statement). As for the limits you spoke of, 100% agree... 24, 25, and even 100 months is too little.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2018 at 11:05 am
below86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.
Intentionally limited to 100 months. But very easy to add CROSS JOINs to the tally table to generate any number of months you could ever use (1M is easy enough).
I. like (almost?) everyone else here, would never use a loop -- especially with a hard-coded GOTO (yikes!) -- in place of a tally-based method.
I almost don't like "tricky" code, like using day of "-1", etc., since it (1) tends to add bugs (as here) and (2) is much harder to understand, and thus to accurately replicate or modify. It's easy enough to get the end of a month without all the obscure coding.
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".
January 22, 2018 at 11:35 am
ScottPletcher - Monday, January 22, 2018 11:05 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.Intentionally limited to 100 months. But very easy to add CROSS JOINs to the tally table to generate any number of months you could ever use (1M is easy enough).
I. like (almost?) everyone else here, would never use a loop -- especially with a hard-coded GOTO (yikes!) -- in place of a tally-based method.
I almost don't like "tricky" code, like using day of "-1", etc., since it (1) tends to add bugs (as here) and (2) is much harder to understand, and thus to accurately replicate or modify. It's easy enough to get the end of a month without all the obscure coding.
Actually, the bug wasn't caused by the day of "-1". If it really bothers you, -1 is '1899-12-31'. I'm also surprised by the number of people who have no problem with using 0 for '1900-01-01' but have issues with using -1 for '1899-12-31'.
The initial bug was introduced by Chris by assuming that simply adding/subtracting a month from the current month end would produce the next/previous month end. I rewrote that portion to always produce the actual month end for a given month, but did not adjust for the fact that his beginning of the month calculations were also based on the length of the current month. (Yes, I didn't test thoroughly. I would never have written the code that way in the first place.)
As to the number of records produced, again, I was modifying Chris' code. If I were writing it from scratch, I would have used something closer to Scott's approach, which would have allowed for a variable number of months.
Once I added a cross join to Scott's and tested, it ran in about 1/8 the time of the loop for 1000 records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 22, 2018 at 12:14 pm
drew.allen - Monday, January 22, 2018 11:35 AMScottPletcher - Monday, January 22, 2018 11:05 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.Intentionally limited to 100 months. But very easy to add CROSS JOINs to the tally table to generate any number of months you could ever use (1M is easy enough).
I. like (almost?) everyone else here, would never use a loop -- especially with a hard-coded GOTO (yikes!) -- in place of a tally-based method.
I almost don't like "tricky" code, like using day of "-1", etc., since it (1) tends to add bugs (as here) and (2) is much harder to understand, and thus to accurately replicate or modify. It's easy enough to get the end of a month without all the obscure coding.
Actually, the bug wasn't caused by the day of "-1". If it really bothers you, -1 is '1899-12-31'. I'm also surprised by the number of people who have no problem with using 0 for '1900-01-01' but have issues with using -1 for '1899-12-31'.
The initial bug was introduced by Chris by assuming that simply adding/subtracting a month from the current month end would produce the next/previous month end. I rewrote that portion to always produce the actual month end for a given month, but did not adjust for the fact that his beginning of the month calculations were also based on the length of the current month. (Yes, I didn't test thoroughly. I would never have written the code that way in the first place.)
As to the number of records produced, again, I was modifying Chris' code. If I were writing it from scratch, I would have used something closer to Scott's approach, which would have allowed for a variable number of months.
Once I added a cross join to Scott's and tested, it ran in about 1/8 the time of the loop for 1000 records.
Drew
I know what date -1 is, I still object to that "cutesy" method of backing up a day as opposed to a clear DATEADD(DAY, -1, ...). Not to mention possibly introducing errors if smalldatetimes are used with a date before 1900. Any experienced developer or DBA would know what date 0 is, but they might have doubts about day -1 and would feel compelled to spend time running code to verify the result -- to me, that's wasted time for no real reason. Besides, negative numbers are just not as inherently easy for people to use as positive ones, similar to negative logic.
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".
January 22, 2018 at 12:49 pm
I would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.
All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 22, 2018 at 1:05 pm
below86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
I'm not saying coding a loop is "tricky", just that it's very poor technique, particularly in a set-based language like T-SQL. GOTO logic is notoriously difficult to maintain; even decent COBOL coders severely restricted GO TO (ONLY to a PERFORM EXIT, period!).
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".
January 22, 2018 at 1:16 pm
ScottPletcher - Monday, January 22, 2018 1:05 PMbelow86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
I'm not saying coding a loop is "tricky", just that it's very poor technique, particularly in a set-based language like T-SQL. GOTO logic is notoriously difficult to maintain; even decent COBOL coders severely restricted GO TO (ONLY to a PERFORM EXIT, period!).
Aw yes, GOTO in COBOL would be a no no, it's been about 15 years since I coded in COBOL. But my point is in COBOL you would set up logic to perform the same task over and over(in a loop).Do While?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 22, 2018 at 1:25 pm
below86 - Monday, January 22, 2018 1:16 PMScottPletcher - Monday, January 22, 2018 1:05 PMbelow86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
I'm not saying coding a loop is "tricky", just that it's very poor technique, particularly in a set-based language like T-SQL. GOTO logic is notoriously difficult to maintain; even decent COBOL coders severely restricted GO TO (ONLY to a PERFORM EXIT, period!).
Aw yes, GOTO in COBOL would be a no no, it's been about 15 years since I coded in COBOL. But my point is in COBOL you would set up logic to perform the same task over and over(in a loop).Do While?
SQL Server is based on set theory and is optimized for set-based operations. Because of that, T-SQL programming is very different from programming in more procedural languages like the ones you mention. A row-by-row approach - such as your loop - prevents SQL Server from using any of those optimizations.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 22, 2018 at 1:29 pm
ScottPletcher - Monday, January 22, 2018 12:14 PMdrew.allen - Monday, January 22, 2018 11:35 AMScottPletcher - Monday, January 22, 2018 11:05 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.Intentionally limited to 100 months. But very easy to add CROSS JOINs to the tally table to generate any number of months you could ever use (1M is easy enough).
I. like (almost?) everyone else here, would never use a loop -- especially with a hard-coded GOTO (yikes!) -- in place of a tally-based method.
I almost don't like "tricky" code, like using day of "-1", etc., since it (1) tends to add bugs (as here) and (2) is much harder to understand, and thus to accurately replicate or modify. It's easy enough to get the end of a month without all the obscure coding.
Actually, the bug wasn't caused by the day of "-1". If it really bothers you, -1 is '1899-12-31'. I'm also surprised by the number of people who have no problem with using 0 for '1900-01-01' but have issues with using -1 for '1899-12-31'.
The initial bug was introduced by Chris by assuming that simply adding/subtracting a month from the current month end would produce the next/previous month end. I rewrote that portion to always produce the actual month end for a given month, but did not adjust for the fact that his beginning of the month calculations were also based on the length of the current month. (Yes, I didn't test thoroughly. I would never have written the code that way in the first place.)
As to the number of records produced, again, I was modifying Chris' code. If I were writing it from scratch, I would have used something closer to Scott's approach, which would have allowed for a variable number of months.
Once I added a cross join to Scott's and tested, it ran in about 1/8 the time of the loop for 1000 records.
DrewI know what date -1 is, I still object to that "cutesy" method of backing up a day as opposed to a clear DATEADD(DAY, -1, ...). Not to mention possibly introducing errors if smalldatetimes are used with a date before 1900. Any experienced developer or DBA would know what date 0 is, but they might have doubts about day -1 and would feel compelled to spend time running code to verify the result -- to me, that's wasted time for no real reason. Besides, negative numbers are just not as inherently easy for people to use as positive ones, similar to negative logic.
I would argue that using the integer representation of a date to refer to a date is what makes it "cutesy" and that using 0 to refer to 1900-01-01 is just as "cutesy" as using -1 to refer to 1899-12-31. I would also argue that any experienced developer or DBA should have no problem figuring out what date is represented by any integer from -1 through 30.
I also think it's much clearer if you can use fewer calculations just by changing the reference date.
DECLARE @Date DATE = GETDATE(),
@InputDate DATE = GETDATE(),
@ReferenceDate DATETIME = '1976-01-23 12:23:56.789'
SELECT
/* This expression uses only a DATEADD and a DATEDIFF */
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1),
/* This expression adds an addition operation, because it
uses a different (wrong) reference date in the DATEDIFF. */
DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) + 1, -1),
/* This expressions adds another DATEADD, because it uses
a different (wrong) reference date in both the DATEDIFF
and the original DATEADD. */
DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0, @Date)+1,0)),
The following are all examples of the exact same formula. If you understand how one works, you should have no problem
understanding the others.
DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0),
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1),
DATEADD(MONTH, DATEDIFF(MONTH, @ReferenceDate, @InputDate), @ReferenceDate)
The key to using this formula is understanding which reference date you should use to most easily get the output that you want. Unfortunately, most people have memorized the first instance of the formula and haven't extrapolated to the more general instance of the formula, so they are stuck doing more complex computations than are necessary.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 22, 2018 at 1:43 pm
drew.allen - Monday, January 22, 2018 1:25 PMbelow86 - Monday, January 22, 2018 1:16 PMScottPletcher - Monday, January 22, 2018 1:05 PMbelow86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
I'm not saying coding a loop is "tricky", just that it's very poor technique, particularly in a set-based language like T-SQL. GOTO logic is notoriously difficult to maintain; even decent COBOL coders severely restricted GO TO (ONLY to a PERFORM EXIT, period!).
Aw yes, GOTO in COBOL would be a no no, it's been about 15 years since I coded in COBOL. But my point is in COBOL you would set up logic to perform the same task over and over(in a loop).Do While?
SQL Server is based on set theory and is optimized for set-based operations. Because of that, T-SQL programming is very different from programming in more procedural languages like the ones you mention. A row-by-row approach - such as your loop - prevents SQL Server from using any of those optimizations.Drew
I did say that a loop may not be the best solution for doing this in SQL. My point was on the 'tricky' coding techniques. Where a novice of someone unfamiliar with SQL could understand the loop SQL, I doubt they could any of the others. Since the performance difference in my testing of these is negligible, that's why I went with the simpler code(IMHO).
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 22, 2018 at 1:50 pm
drew.allen - Monday, January 22, 2018 1:29 PMScottPletcher - Monday, January 22, 2018 12:14 PMdrew.allen - Monday, January 22, 2018 11:35 AMScottPletcher - Monday, January 22, 2018 11:05 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.Intentionally limited to 100 months. But very easy to add CROSS JOINs to the tally table to generate any number of months you could ever use (1M is easy enough).
I. like (almost?) everyone else here, would never use a loop -- especially with a hard-coded GOTO (yikes!) -- in place of a tally-based method.
I almost don't like "tricky" code, like using day of "-1", etc., since it (1) tends to add bugs (as here) and (2) is much harder to understand, and thus to accurately replicate or modify. It's easy enough to get the end of a month without all the obscure coding.
Actually, the bug wasn't caused by the day of "-1". If it really bothers you, -1 is '1899-12-31'. I'm also surprised by the number of people who have no problem with using 0 for '1900-01-01' but have issues with using -1 for '1899-12-31'.
The initial bug was introduced by Chris by assuming that simply adding/subtracting a month from the current month end would produce the next/previous month end. I rewrote that portion to always produce the actual month end for a given month, but did not adjust for the fact that his beginning of the month calculations were also based on the length of the current month. (Yes, I didn't test thoroughly. I would never have written the code that way in the first place.)
As to the number of records produced, again, I was modifying Chris' code. If I were writing it from scratch, I would have used something closer to Scott's approach, which would have allowed for a variable number of months.
Once I added a cross join to Scott's and tested, it ran in about 1/8 the time of the loop for 1000 records.
DrewI know what date -1 is, I still object to that "cutesy" method of backing up a day as opposed to a clear DATEADD(DAY, -1, ...). Not to mention possibly introducing errors if smalldatetimes are used with a date before 1900. Any experienced developer or DBA would know what date 0 is, but they might have doubts about day -1 and would feel compelled to spend time running code to verify the result -- to me, that's wasted time for no real reason. Besides, negative numbers are just not as inherently easy for people to use as positive ones, similar to negative logic.
I would argue that using the integer representation of a date to refer to a date is what makes it "cutesy" and that using 0 to refer to 1900-01-01 is just as "cutesy" as using -1 to refer to 1899-12-31. I would also argue that any experienced developer or DBA should have no problem figuring out what date is represented by any integer from -1 through 30.
I also think it's much clearer if you can use fewer calculations just by changing the reference date.
DECLARE @Date DATE = GETDATE(),
@InputDate DATE = GETDATE(),
@ReferenceDate DATETIME = '1976-01-23 12:23:56.789'SELECT
/* This expression uses only a DATEADD and a DATEDIFF */
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1),/* This expression adds an addition operation, because it
uses a different (wrong) reference date in the DATEDIFF. */
DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) + 1, -1),/* This expressions adds another DATEADD, because it uses
a different (wrong) reference date in both the DATEDIFF
and the original DATEADD. */
DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0, @Date)+1,0)),The following are all examples of the exact same formula. If you understand how one works, you should have no problem
understanding the others.
DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0),
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1),
DATEADD(MONTH, DATEDIFF(MONTH, @ReferenceDate, @InputDate), @ReferenceDate)The key to using this formula is understanding which reference date you should use to most easily get the output that you want. Unfortunately, most people have memorized the first instance of the formula and haven't extrapolated to the more general instance of the formula, so they are stuck doing more complex computations than are necessary.
Drew
Using 0 for a date is common enough that it's not "cutesy". But I have no objection to someone who prefers to code '19000101'. EIther is common enough in SQL usage.
The "standard", best-practice method for getting a starting time period is:
DATEADD(<time_period>, DATEDIFF(<time_period>, 0, <date_value> ), 0)
So for start of today is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE(), 0)
Similarly, for start of year is:
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE(), 0)
A consistent formula. Additional integer adds are hardly "complex" and are so little overhead I doubt it can be measured. The standard formula, by your own admission, is the one most people already automatically understand, That clarity and consistency is more valuable than the "overhead" of a couple of integer calcs. Especially on something like this:
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1)
vs
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0))
A single computation against a scalar variable? I'll choose the second every time. It follows the automatically-recognized pattern, thus speeding comprehension of the base expression, after which mental on-the-fly adjustments are easy.
At any rate, say if I want to get the 15th of the month, what negative number do I put in your formula to avoid "extra calcs"? Anyone would instantly know in my style that you'd just add 14 days, but I'm interested in hearing how that negative number would be determined.
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".
January 22, 2018 at 2:10 pm
ScottPletcher - Monday, January 22, 2018 1:50 PMAt any rate, say if I want to get the 15th of the month, what negative number do I put in your formula to avoid "extra calcs"? Anyone would instantly know in my style that you'd just add 14 days, but I'm interested in hearing how that negative number would be determined.
You don't. You put in a reference date that has the property that you are trying to preserve. ANY reference date that has that property will be acceptable. In this case, the property that you are trying to preserve is the 15th of the month, so one such formula would be.
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '2345-01-15', GETDATE()), '2345-01-15')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 22, 2018 at 2:26 pm
drew.allen - Monday, January 22, 2018 2:10 PMScottPletcher - Monday, January 22, 2018 1:50 PMAt any rate, say if I want to get the 15th of the month, what negative number do I put in your formula to avoid "extra calcs"? Anyone would instantly know in my style that you'd just add 14 days, but I'm interested in hearing how that negative number would be determined.You don't. You put in a reference date that has the property that you are trying to preserve. ANY reference date that has that property will be acceptable. In this case, the property that you are trying to preserve is the 15th of the month, so one such formula would be.
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '2345-01-15', GETDATE()), '2345-01-15')Drew
I figured it would be:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 14, GETDATE()), 14)
which, yes, saves a calc, but is it really as clear? I still don't think so. The recognized pattern is clearer to most people, as you yourself admitted above.
Didn't someone famous once say? 😉:
"Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms."
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".
Viewing 15 posts - 16 through 30 (of 104 total)
You must be logged in to reply to this topic. Login to reply