January 23, 2018 at 5:54 pm
ScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 -- not to mention YYYY-00-00 -- raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?Also, is this now valid:
WHERE column_date >= '2018-01-00' AND column_date < '2018-02-00'
?
What specifically does it mean?
These are called temporal interval datatypes, to go with date, time and timestamp datatypes. They represent an interval, not an atomic date. I mean for example a DATE datatype is really a shorthand for the range from 00:00:00 to 23:59:59.99999.. on that date.
Right now you should get a syntax error, because the ANSI/ISO standards don't support this notation. I'm also not sure if any of the proposals specify how to handle BETWEEN and comparisons using this notation. I would assume that the same as any other interval data type or behaves the way that date does currently.
I honestly don't know what the status is inside ISO – 8601; I never worked with that committee.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 23, 2018 at 9:06 pm
jcelko212 32090 - Tuesday, January 23, 2018 5:54 PMScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 -- not to mention YYYY-00-00 -- raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?Also, is this now valid:
WHERE column_date >= '2018-01-00' AND column_date < '2018-02-00'
?
What specifically does it mean?These are called temporal interval datatypes, to go with date, time and timestamp datatypes. They represent an interval, not an atomic date. I mean for example a DATE datatype is really a shorthand for the range from 00:00:00 to 23:59:59.99999.. on that date.
Right now you should get a syntax error, because the ANSI/ISO standards don't support this notation. I'm also not sure if any of the proposals specify how to handle BETWEEN and comparisons using this notation. I would assume that the same as any other interval data type or behaves the way that date does currently.
I honestly don't know what the status is inside ISO – 8601; I never worked with that committee.
So, in such a case (and bear with me a minute) and if such temporal interval datatypes existed and the variables in the following SQL had been previously assigned as the correct temporal interval datatype, that the following would return a row containing "They are equal."?
SET @a = '19000101';
SET @b-2 = '19000101 23:59:59';
SELECT 'They are equal.'
WHERE @a = @b-2;
Also, it's nice to understand that a calendar date includes any part of the timespan from 00:00:00 to 23:59:59.99999 according to human logic/perception but the DATE datatype doesn't come close to that functionality without some help.
DECLARE @a DATE = '19000101 23:59:59.000'
,@B DATETIME2(0) = '19000101 23:59:59.000'
;
SELECT 'They are equal.'
WHERE @a = @b-2
;
(0 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2018 at 9:22 pm
Lynn Pettis - Tuesday, January 23, 2018 4:11 PMOh, when you go back past 1753 the dates may not actually be correct. I leave that to you to research.
And with that, we come full circle back to my original question. WHAT are these dates going to be used for and HOW are they going to be used? The OP previously responded to that by saying it's for "other processes" but that doesn't explain HOW they are going to be used. For example, if the generated data is going to be used as a grouping join for DATETIME data, then calculating the end of the month instead of the beginning of the next month is absolutely the wrong thing to do. Further, using such a table for a grouping join may not actually be the best method to use depending on the overriding task at hand but we've not been given a clue as to what that big picture is.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2018 at 9:34 pm
below86 - Tuesday, January 23, 2018 3:33 PMThanks Lynn for the code, more than what Jeff did. 😀 That's a joke Jeff, you given me plenty to digest. "handy man's secret weapon".
Heh... I get it.
To be honest, I asked the OP a question very early on in this thread and then, when the OP gave a totally non-descript answer, I asked again. The OP still hasn't identified HOW this data will be used and there's actually a rather huge risk to the OP by using EOM instead of the beginning of the next month depending on HOW the data is actually going to be used. Shoot, this derived table that we're generating for the OP might be one of the worst solutions for the actual problem at hand, whatever that happens to be. That's why I haven't posted any code, yet.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2018 at 11:29 pm
jcelko212 32090 - Tuesday, January 23, 2018 5:54 PMScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 -- not to mention YYYY-00-00 -- raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?Also, is this now valid:
WHERE column_date >= '2018-01-00' AND column_date < '2018-02-00'
?
What specifically does it mean?These are called temporal interval datatypes, to go with date, time and timestamp datatypes. They represent an interval, not an atomic date. I mean for example a DATE datatype is really a shorthand for the range from 00:00:00 to 23:59:59.99999.. on that date.
Right now you should get a syntax error, because the ANSI/ISO standards don't support this notation. I'm also not sure if any of the proposals specify how to handle BETWEEN and comparisons using this notation. I would assume that the same as any other interval data type or behaves the way that date does currently.
I honestly don't know what the status is inside ISO – 8601; I never worked with that committee.
In this scenario it appears to me that you now have a data type of what a procedure language might classify as TIME [ ] ... That is a array of of type TIME, consisting of all possible time values. What if I only want the first half of those? What is the size of my array? I like that SQL Server considers time to be an infinitesimal point, and represents it in memory as such to the best of its ability. Keeps things simple as I see it.
----------------------------------------------------
January 24, 2018 at 3:00 am
So 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.
_____________
Code for TallyGenerator
January 24, 2018 at 6:54 am
Sergiy - 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.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 24, 2018 at 8:00 am
jcelko212 32090 - Tuesday, January 23, 2018 5:54 PMScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 -- not to mention YYYY-00-00 -- raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?Also, is this now valid:
WHERE column_date >= '2018-01-00' AND column_date < '2018-02-00'
?
What specifically does it mean?These are called temporal interval datatypes, to go with date, time and timestamp datatypes. They represent an interval, not an atomic date. I mean for example a DATE datatype is really a shorthand for the range from 00:00:00 to 23:59:59.99999.. on that date.
Right now you should get a syntax error, because the ANSI/ISO standards don't support this notation. I'm also not sure if any of the proposals specify how to handle BETWEEN and comparisons using this notation. I would assume that the same as any other interval data type or behaves the way that date does currently.
I honestly don't know what the status is inside ISO – 8601; I never worked with that committee.
Quite right. That's why I was asking if yet some new data type will be invented that stores a "date" as YYYY-MM-00 and YYYY-00-00. Those values do not fit into any current data type that I'm aware of. And it seems to me that they violate domain rules for a "date".
I wanted to make clear I'm not against an "interval" type, but that's not what this is. It's some new beast, a chimera even.
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:37 am
Sergiy - 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.
Exactly... especially on the " >= BOM and < BO next M " thing. There may be an exception when that's not the right way to do it but I can't think of one.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2018 at 8:50 am
Jeff Moden - Wednesday, January 24, 2018 8:37 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.Exactly... especially on the " >= BOM and < BO next M " thing. There may be an exception when that's not the right way to do it but I can't think of one.
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.
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:59 am
below86 - 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2018 at 9:01 am
Lynn 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.
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:20 am
ScottPletcher - 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.
January 24, 2018 at 9:21 am
ScottPletcher - 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2018 at 9:26 am
Lynn 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.
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 - 61 through 75 (of 104 total)
You must be logged in to reply to this topic. Login to reply