January 24, 2018 at 9:28 am
Jeff Moden - Wednesday, January 24, 2018 9:21 AMScottPletcher - Wednesday, January 24, 2018 9:01 AMLynn Pettis - Tuesday, January 23, 2018 4:10 PMScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
Id' rather avoid the code error, and possible production failure, when some adds a statement before the cte and forgets to include the semicolon. Productivity trumps pedantry for me here; it's just not worth the hassle of explaining the error afterward.
While I'm definitely not a part of the "anti-begininator" crowd (although it does bug me) and the method you use does have incredible merit, it's a sign of the larger problems of someone deploying unreviewed and untested code to production.
I also wonder if and when Microsoft will actually finalize their very old and long running threat through deprecation notices to actually require semi-colons in all the right places. Every time we work on a piece of legacy code, part of the job and part of the review before going to testing is to add any and all missing semi-colons.
In theory testing should catch it, but we use almost all dynamic SQL (for valid reasons), and it can be rather difficult to test all variations, esp. since we don't currently have all test cases defined as part of our testing setup.
I wish we had the time to add semicolons to all existing code, but frankly we just don't. We're adding big clients like crazy, and management would laugh me out of the room if I tried to argue for significant time to "fix" something that wasn't actually broken (I hope all they'd do is laugh, and not boot my a$$ out the door for wasting their time).
When MS does finally require it:
1) many people will avoid upgrading simply to avoid having to comply with it (one more reason they likely won't require it anytime soon)
2) somebody will release a tool that will make the changes for you
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 24, 2018 at 9:33 am
ScottPletcher - Wednesday, January 24, 2018 9:26 AMLynn Pettis - Wednesday, January 24, 2018 9:20 AMScottPletcher - Wednesday, January 24, 2018 9:01 AMLynn Pettis - Tuesday, January 23, 2018 4:10 PMScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
Id' rather avoid the code error, and possible production failure, when some adds a statement before the cte and forgets to include the semicolon. Productivity trumps pedantry for me here; it's just not worth the hassle of explaining the error afterward.
Simple solution, require all statements be terminated with a semicolon. Considering some statements, like MERGE, require a terminating semicolon.
"Simple"?? Not at all! How does one verify that? We use almost all dynamic SQL (for valid reasons, particularly given when the code was originally architected).
Even then, it's not worth the hundreds of hours it would take us to remediate all our code to add terminating chars, esp. given that it provides zero genuine value to our clients or our company. The solution that actually is simple is to give up being a semicolon fanatic. Yes, strongly encourage their use, yes, actively push for it, but don't cause company failures just to prove some point about it.
Yes, simple. I write a lot of dynamic SQL and I ensure that statements are properly terminated with semicolons in my dynamic SQL as well as normal SQL. If you make it a part of your corporate standard it isn't difficult. And since you never know when Microsoft may actually fully deprecate the optional use of semicolons to terminate statements it makes sense to use them in all cases.
January 24, 2018 at 9:34 am
ScottPletcher - Wednesday, January 24, 2018 9:28 AMJeff Moden - Wednesday, January 24, 2018 9:21 AMScottPletcher - Wednesday, January 24, 2018 9:01 AMLynn Pettis - Tuesday, January 23, 2018 4:10 PMScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
Id' rather avoid the code error, and possible production failure, when some adds a statement before the cte and forgets to include the semicolon. Productivity trumps pedantry for me here; it's just not worth the hassle of explaining the error afterward.
While I'm definitely not a part of the "anti-begininator" crowd (although it does bug me) and the method you use does have incredible merit, it's a sign of the larger problems of someone deploying unreviewed and untested code to production.
I also wonder if and when Microsoft will actually finalize their very old and long running threat through deprecation notices to actually require semi-colons in all the right places. Every time we work on a piece of legacy code, part of the job and part of the review before going to testing is to add any and all missing semi-colons.
In theory testing should catch it, but we use almost all dynamic SQL (for valid reasons), and it can be rather difficult to test all variations, esp. since we don't currently have all test cases defined as part of our testing setup.
I wish we had the time to add semicolons to all existing code, but frankly we just don't. We're adding big clients like crazy, and they'd laugh me out of the room if I tried to argue for time to "fix" something that wasn't actually broken (I hope all they'd do is laugh, and not boot my a$$ out the door for wasting their time).
Understood and very much appreciated on both of those problems. In the business we're in, we have to test all the variations no matter how long it takes because it's financial in nature and has to be compliant with a half dozen groups such as the SEC, a half dozen mortgage protection groups, and more. We simply don't have the luxury to not test all of the variations (and, yes, we necessarily use a shedload of Dynamic SQL, as well)
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2018 at 9:36 am
ScottPletcher - Wednesday, January 24, 2018 9:28 AMJeff Moden - Wednesday, January 24, 2018 9:21 AMScottPletcher - Wednesday, January 24, 2018 9:01 AMLynn Pettis - Tuesday, January 23, 2018 4:10 PMScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
Id' rather avoid the code error, and possible production failure, when some adds a statement before the cte and forgets to include the semicolon. Productivity trumps pedantry for me here; it's just not worth the hassle of explaining the error afterward.
While I'm definitely not a part of the "anti-begininator" crowd (although it does bug me) and the method you use does have incredible merit, it's a sign of the larger problems of someone deploying unreviewed and untested code to production.
I also wonder if and when Microsoft will actually finalize their very old and long running threat through deprecation notices to actually require semi-colons in all the right places. Every time we work on a piece of legacy code, part of the job and part of the review before going to testing is to add any and all missing semi-colons.
In theory testing should catch it, but we use almost all dynamic SQL (for valid reasons), and it can be rather difficult to test all variations, esp. since we don't currently have all test cases defined as part of our testing setup.
I wish we had the time to add semicolons to all existing code, but frankly we just don't. We're adding big clients like crazy, and management would laugh me out of the room if I tried to argue for significant time to "fix" something that wasn't actually broken (I hope all they'd do is laugh, and not boot my a$$ out the door for wasting their time).
When MS does finally require it:
1) many people will avoid upgrading simply to avoid having to comply with it (one more reason they likely won't require it anytime soon)
2) somebody will release a tool that will make the changes for you
Regarding number 2, yes there is, it is called SQL Prompt. It can add them for you quite easily. My company recently purchased Redgate Toolkit Essentials and being able to format code with ctrl-k + ctrl-y makes it easy.
January 24, 2018 at 9:37 am
Lynn Pettis - Wednesday, January 24, 2018 9:33 AMScottPletcher - Wednesday, January 24, 2018 9:26 AMLynn Pettis - Wednesday, January 24, 2018 9:20 AMScottPletcher - Wednesday, January 24, 2018 9:01 AMLynn Pettis - Tuesday, January 23, 2018 4:10 PMScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
Id' rather avoid the code error, and possible production failure, when some adds a statement before the cte and forgets to include the semicolon. Productivity trumps pedantry for me here; it's just not worth the hassle of explaining the error afterward.
Simple solution, require all statements be terminated with a semicolon. Considering some statements, like MERGE, require a terminating semicolon.
"Simple"?? Not at all! How does one verify that? We use almost all dynamic SQL (for valid reasons, particularly given when the code was originally architected).
Even then, it's not worth the hundreds of hours it would take us to remediate all our code to add terminating chars, esp. given that it provides zero genuine value to our clients or our company. The solution that actually is simple is to give up being a semicolon fanatic. Yes, strongly encourage their use, yes, actively push for it, but don't cause company failures just to prove some point about it.
Yes, simple. I write a lot of dynamic SQL and I ensure that statements are properly terminated with semicolons in my dynamic SQL as well as normal SQL. If you make it a part of your corporate standard it isn't difficult. And since you never know when Microsoft may actually fully deprecate the optional use of semicolons to terminate statements it makes sense to use them in all cases.
How many developers are in your staff? You personally "ensure" that a dozen+ people are all using semicolons in all the right places in all their code? Even if I had the time to do that -- and as I said, I certainly don't -- I wouldn't, because I have a list of 100 other things that would be of far more value.
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 24, 2018 at 9:50 am
ScottPletcher - Wednesday, January 24, 2018 9:37 AMLynn Pettis - Wednesday, January 24, 2018 9:33 AMScottPletcher - Wednesday, January 24, 2018 9:26 AMLynn Pettis - Wednesday, January 24, 2018 9:20 AMScottPletcher - Wednesday, January 24, 2018 9:01 AMLynn Pettis - Tuesday, January 23, 2018 4:10 PMScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
Id' rather avoid the code error, and possible production failure, when some adds a statement before the cte and forgets to include the semicolon. Productivity trumps pedantry for me here; it's just not worth the hassle of explaining the error afterward.
Simple solution, require all statements be terminated with a semicolon. Considering some statements, like MERGE, require a terminating semicolon.
"Simple"?? Not at all! How does one verify that? We use almost all dynamic SQL (for valid reasons, particularly given when the code was originally architected).
Even then, it's not worth the hundreds of hours it would take us to remediate all our code to add terminating chars, esp. given that it provides zero genuine value to our clients or our company. The solution that actually is simple is to give up being a semicolon fanatic. Yes, strongly encourage their use, yes, actively push for it, but don't cause company failures just to prove some point about it.
Yes, simple. I write a lot of dynamic SQL and I ensure that statements are properly terminated with semicolons in my dynamic SQL as well as normal SQL. If you make it a part of your corporate standard it isn't difficult. And since you never know when Microsoft may actually fully deprecate the optional use of semicolons to terminate statements it makes sense to use them in all cases.
How many developers are in your staff? You personally "ensure" that a dozen+ people are all using semicolons in all the right places in all their code? Even if I had the time to do that -- and as I said, I certainly don't -- I wouldn't, because I have a list of 100 other things that would be of far more value.
First of all, nope, I rarely get to see any of the SQL code written by java/cold fusion developers until it has gone through testing, ipat, and deployed to production sites. When customers complain about slow code is the first time I get to see the code that was deployed because I support the people out in the field, not the developers. Sucks but it is what it is and we have tried changing it but to little avail. The code I write, yes, I make sure all statements are terminated with semicolons. Code I rewrite to improve things in our deployed systems, yes I make sure that all statements are terminated by semicolons. There are a couple of people that also write SQL (and still shouldn't) that have begun to do the same because they have seen the code I write and have started doing it as well, probably because those few know I will see the code they write as the code they write.
No one is saying to go through all your code to add semicolons, but if you are making changes to a piece of code that is the time to ensure all statements are properly terminated. Of course, this is all up to you and your company.
January 24, 2018 at 9:59 am
Jeff Moden - Wednesday, January 24, 2018 8:59 AMbelow86 - Wednesday, January 24, 2018 6:54 AMSergiy - Wednesday, January 24, 2018 3:00 AMSo many solutions of different quality to the problem which should never be attempted!Jeff asked a question at the very beginning, but the topic got spammed by numerous solutions which all have zero value, because having EOM dates has zero value by itself.I can see only 2 points of having EOM in date range selection:- complicate queries;- introduce errors.All dates for any month fit into interval " >= BOM and < BO next M "EOM date is absolutely useless thing, therefore all solution to the task of populating a set of EOM's are as useless.I wouldn't say completely useless. When we store our data in our warehouse we set an accounting date(EOM date) for those rows of data processed in that month. The same can be said for the daily transactions, the accounting date is just the date, no time, of the day it was processed, another fields carries the actual date and time stamp for that transaction. So everything we pull for the month we could use the BOM and EOM dates in such a table.
The problem with that is that you do have to know the EOM, which varies month to month and Leap Years cause an additional change. While it may be working for you, the first of the next month will never change. The classic "somedatecol >= BOM AND somedatecol < BOnextM" works for everything the same way all the time with or without times and also makes code bullet proof in the face of any changes that may occur (I've seen many people get burned by that especially when a lot of folks made the too-cool-for-school "me too" change from DATETIME to DATETIME2). It also helps those Developers that you spoke of before because it's consistently accurate and no one actually has to research as to whether or not EOM is being used or not and requires absolutely no knowledge of the precision of the underlying data and requires absolutely no knowledge of EOM.
I have to agree with Sergiy... although it's not useless to you because of the throws you good folks have gone through to use some form of EOM, using any form of EOM and BETWEEN for temporal criteria to isolate intervals, such a months, is totally useless to me because I flat refuse to go through the throws necessary to use either for such things, especially when the classic solution is so very simple and guaranteed to always work (and I don't use the word "always" very often). Consider avoiding both like the plague for your future coding efforts. It will make things a whole lot easier for you.
Using the BOM doesn't make sense for the data we have. If we set the accounting date(EOM date or what ever you want to call it) for the data processed during a month to be the EOM date(no time) then there is no issue. Everything processed in the month of December 2017 would get the date 12/31/2017. From the day our warehouse was set up at my prior job, still that way is far as I know, we Always used the EOM date, I don't remember ever running into an issue.(and I would guess it is about 20 years old now) The only issue you could run into is if you are pulling February data and you don't know if it was a leap year. And that's if you are hard coding the date. February is the only month that you don't Always know what the last day is. So you could guarantee you will Always use the correct EOM date. Simple date logic should insure you get the correct date. Maybe in an ideal world when you get to set up everything from scratch you would follow your logic. But as with my prior job my current one is also using EOM date. So to think I could just decide to start following the logic of using the first of the month in everything I code, doesn't make sense. When there are thousands of lines of code that are based of EOM date it sure would make it confusing for one person(or more) to change how the date logic is coded going forward. And who has the time to go back and fix all that code so we are consistent? (talk about being confusing for developers) I would say we don't, and I doubt anyone would say they have the time either. So 'it depends' on your shop if BOM or EOM is right.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 24, 2018 at 1:33 pm
below86 - Wednesday, January 24, 2018 9:59 AMJeff Moden - Wednesday, January 24, 2018 8:59 AMbelow86 - Wednesday, January 24, 2018 6:54 AMSergiy - Wednesday, January 24, 2018 3:00 AMSo many solutions of different quality to the problem which should never be attempted!Jeff asked a question at the very beginning, but the topic got spammed by numerous solutions which all have zero value, because having EOM dates has zero value by itself.I can see only 2 points of having EOM in date range selection:- complicate queries;- introduce errors.All dates for any month fit into interval " >= BOM and < BO next M "EOM date is absolutely useless thing, therefore all solution to the task of populating a set of EOM's are as useless.I wouldn't say completely useless. When we store our data in our warehouse we set an accounting date(EOM date) for those rows of data processed in that month. The same can be said for the daily transactions, the accounting date is just the date, no time, of the day it was processed, another fields carries the actual date and time stamp for that transaction. So everything we pull for the month we could use the BOM and EOM dates in such a table.
The problem with that is that you do have to know the EOM, which varies month to month and Leap Years cause an additional change. While it may be working for you, the first of the next month will never change. The classic "somedatecol >= BOM AND somedatecol < BOnextM" works for everything the same way all the time with or without times and also makes code bullet proof in the face of any changes that may occur (I've seen many people get burned by that especially when a lot of folks made the too-cool-for-school "me too" change from DATETIME to DATETIME2). It also helps those Developers that you spoke of before because it's consistently accurate and no one actually has to research as to whether or not EOM is being used or not and requires absolutely no knowledge of the precision of the underlying data and requires absolutely no knowledge of EOM.
I have to agree with Sergiy... although it's not useless to you because of the throws you good folks have gone through to use some form of EOM, using any form of EOM and BETWEEN for temporal criteria to isolate intervals, such a months, is totally useless to me because I flat refuse to go through the throws necessary to use either for such things, especially when the classic solution is so very simple and guaranteed to always work (and I don't use the word "always" very often). Consider avoiding both like the plague for your future coding efforts. It will make things a whole lot easier for you.
Using the BOM doesn't make sense for the data we have. If we set the accounting date(EOM date or what ever you want to call it) for the data processed during a month to be the EOM date(no time) then there is no issue. Everything processed in the month of December 2017 would get the date 12/31/2017. From the day our warehouse was set up at my prior job, still that way is far as I know, we Always used the EOM date, I don't remember ever running into an issue.(and I would guess it is about 20 years old now) The only issue you could run into is if you are pulling February data and you don't know if it was a leap year. And that's if you are hard coding the date. February is the only month that you don't Always know what the last day is. So you could guarantee you will Always use the correct EOM date. Simple date logic should insure you get the correct date. Maybe in an ideal world when you get to set up everything from scratch you would follow your logic. But as with my prior job my current one is also using EOM date. So to think I could just decide to start following the logic of using the first of the month in everything I code, doesn't make sense. When there are thousands of lines of code that are based of EOM date it sure would make it confusing for one person(or more) to change how the date logic is coded going forward. And who has the time to go back and fix all that code so we are consistent? (talk about being confusing for developers) I would say we don't, and I doubt anyone would say they have the time either. So 'it depends' on your shop if BOM or EOM is right.
If I was in this shop I would start using the simple yet powerful >= "BOM" and < "BO the next month". I would start somewhere and illustrate the example to others. Sure you can leave what is working as is, but I dont see how the syntax here would break anything employing going forward. EOM if only in DATE type can make it difficult to reconcile aggregations when you are comparing to sets in a different time zone, say offset by a few hours. With the < operator I dont have to worry since I can use a datetime datetype.
----------------------------------------------------
January 24, 2018 at 2:56 pm
I just don't get it. Why if there is a field that will only contain the EOM date would I want to use that logic?
Why do this:
Where DateField >= '12/01/2017 and DateField < '01/01/2018'
When this gets the same thing:
Where DateField = '12/31/2017'
DateFiled would NEVER contain any other date in December.
In this example DateField can ONLY have 12 different dates for a given year, the EOM date.
Again 'it depends' applies here, not an Always.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 24, 2018 at 3:11 pm
Sometimes when joining, it's easier if the ending value is in the row itself. It's not necessarily the end of the world to include an ending value in the row. And/or maybe there's already a huge amount of code that expects that value to be there. As we all know, often you can't always correct earlier code if there's not enough payback to doing so.
It always there, considering BOM is used.
BO next M is always DATEADD(mm, 1, BOM)
As for existing code - EOM is a last moment before the BO nextM. It is different value for different data types and different uses.
So, having EOM in a table which would be correct for all different uses is simply impossible.
Therefore, doing this is not only useless, it's quite dangerous.
_____________
Code for TallyGenerator
January 24, 2018 at 3:24 pm
When there are thousands of lines of code that are based of EOM date it sure would make it confusing for one person(or more) to change how the date logic is coded going forward. And who has the time to go back and fix all that code so we are consistent?
Listen to yourself.
They've got thousands lines of code relying on correct EOM date, and only now they started looking for a way to find those correct EOM dates?
Really???
_____________
Code for TallyGenerator
January 24, 2018 at 3:51 pm
Sergiy - Wednesday, January 24, 2018 3:11 PMSometimes when joining, it's easier if the ending value is in the row itself. It's not necessarily the end of the world to include an ending value in the row. And/or maybe there's already a huge amount of code that expects that value to be there. As we all know, often you can't always correct earlier code if there's not enough payback to doing so.
It always there, considering BOM is used.BO next M is always DATEADD(mm, 1, BOM)As for existing code - EOM is a last moment before the BO nextM. It is different value for different data types and different uses.So, having EOM in a table which would be correct for all different uses is simply impossible.Therefore, doing this is not only useless, it's quite dangerous.
Not at all. Again, it's quite useful when joining to ranged dates to have the end date directly in the row.
Say we have your table with only beginning dates. Now I want to join to that table. I have to retrieve the next row to do the join. Admittedly not nearly as difficult or expensive when LEAD is available, but still rather a pain.
It's much more of a pain if say next month they decide to change from months to 14-day periods, or even to some dynamic ranges, such as with fuel prices (we have to make mods to fuel prices all the time based on start and end date). With the end date in the row, you simply generate new rows accordingly, at the start, and you're done. The end date should not be redetermined by every piece of code that uses the table, instead only once when the table is generated, or you accept that you will always need to "peek" at the next row to determine the correct range.
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 24, 2018 at 8:28 pm
Say we have your table with only beginning dates. Now I want to join to that table. I have to retrieve the next row to do the join. Admittedly not nearly as difficult or expensive when LEAD is available, but still rather a pain.
You don't need a next row.
You need to add a month to the BOM data which is right there, in this row.
The end date should not be redetermined by every piece of code that uses the table
Not sure what are you referring to, but the formula
= BOM is neither better nor worse than = DATEADD(mm, 1, BOM)
_____________
Code for TallyGenerator
January 24, 2018 at 10:23 pm
below86 - Wednesday, January 24, 2018 2:56 PMI just don't get it. Why if there is a field that will only contain the EOM date would I want to use that logic?
Why do this:
Where DateField >= '12/01/2017 and DateField < '01/01/2018'
When this gets the same thing:
Where DateField = '12/31/2017'DateFiled would NEVER contain any other date in December.
In this example DateField can ONLY have 12 different dates for a given year, the EOM date.
Again 'it depends' applies here, not an Always.
February's end date is not consistent. It WOULD contain another date.
Also remember that in this global economy you can likely get dates coming in from different time zones. Consider ::
Your system is in PT records 12/31 @ say 11:15 pm. Another system that you reconcile against (say this is accounting data) says the transactions happened at 1/1 2:15 am. This is ET where your supplier is and simply records 1/1 in their system. You miss these records if you simply rely on EOM with a simple DATE type (EOM does not work as smoothly with a datetime field). Remember if you are using a Datetime type with EOM, you loose all transactions that did not occur on 12/31 at midnight exactly. Eventually someone figures out this is a time zone issue, you need to change the date type to datetime so as to add three hours to be able to match the two transactions. So now instead of saying give me all records between BOM and EOM, you have to do a little date math to pull the correct set to match against the other system. This is best done with a datetime type. You cant simply put in the expression <= dateadd(hh, 3, eom) as that will result in an error given a Date type.
With a < EO next M , your system is prepared for a move to datetime if needed.
----------------------------------------------------
January 25, 2018 at 7:01 am
Sergiy - Wednesday, January 24, 2018 8:28 PMSay we have your table with only beginning dates. Now I want to join to that table. I have to retrieve the next row to do the join. Admittedly not nearly as difficult or expensive when LEAD is available, but still rather a pain.
You don't need a next row.You need to add a month to the BOM data which is right there, in this row.
The end date should not be redetermined by every piece of code that uses the table
Not sure what are you referring to, but the formula
= BOMis neither better nor worse than= DATEADD(mm, 1, BOM)
No, as in the original intent, I'm referring to a range.
Even if the range doesn't change, to join, you have to use a function on a column:
WHERE date_to_join >= BOM AND date_to_join < DATEADD(MONTH, 1, BOM)
That's no so great for the optimizer in a join.
Far, far worse, you have to code the determination-of-end-date-logic into every piece of code that uses this table.
Wow, that's a ton of work and errors when the range changes: to 2 weeks? Or 3 weeks? Or variable number of days/weeks?
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 - 76 through 90 (of 104 total)
You must be logged in to reply to this topic. Login to reply