May 31, 2018 at 11:25 am
jcelko212 32090 - Thursday, May 31, 2018 11:17 AMYou probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
That's a rather disparaging thing to say, don't you think Celko?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 31, 2018 at 11:25 am
jcelko212 32090 - Thursday, May 31, 2018 11:03 AMPhil Parkin - Wednesday, May 30, 2018 5:21 AMYou could also add the decoded monthname to the CROSS JOIN:CROSS JOIN
(
SELECT m = 1,
mth = 'January'
UNION ALL
SELECT m = 2,
mth = 'February'
UNION ALL
SELECT m = 3,
mth = 'March'
UNION ALL
SELECT m = 4,
mth = 'April'
UNION ALL
SELECT m = 5,
mth = 'May'
UNION ALL
SELECT m = 6,
mth = 'June'
UNION ALL
SELECT m = 7,
mth = 'July'
UNION ALL
SELECT m = 8,
mth = 'August'
UNION ALL
SELECT m = 9,
mth = 'September'
UNION ALL
SELECT m = 10,
mth = 'October'
UNION ALL
SELECT m = 11,
mth = 'November'
UNION ALL
SELECT m = 12,
mth = 'Decemeber'
) cand then change the SELECT bit to have
Period = c.mth,Why are you doing display formatting in the database layer? Isn't that a violation of the basic principle of any tiered architecture? Why are you using the old Sybase UNION ALL syntax to build a table? How can a month exist without being in a year?
Why are you responding to my post with these comments? I'm not the OP.
May 31, 2018 at 11:44 am
jcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AM
Mr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
I suppose in the interest of full disclosure, I did work for the MySQL people and produced some website material. It was all about basic data modeling, etc. Having said this, the reason I keep mentioning their convention for temporal intervals is (1) it is being considered for ANSI ISO standards, thanks largely to the popularity of MySQL (2) it is language independent (3) it sorts properly with the rest of the ISO 8601 display format. In short, they are a good choice at the current time for conforming to international standards.
You probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
Since Microsoft has not implemented the ANSI/ISO standard interval data types in its temporal model, we have to do a "repair job" instead. If you have a better suggestion. I'd love to hear and put it in my books.
REALLY inappropriate thing to say, Joe.
I also hope that the attempt to sanction such non-sense as what they do in MySQL totally fails instantly. ""00" for day or month is inappropriate because there is no such thing as a 0 for day of the month or a month. The use of blanks would make it sort just fine if you were into sorting the mistake dates as character based values. Further, adding "00" to the standard would further perpetuate peoples' mistakes of storing such a thing in a table.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2018 at 11:52 am
Lynn Pettis - Tuesday, May 29, 2018 3:37 PMjcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AMMr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
I suppose in the interest of full disclosure, I did work for the MySQL people and produced some website material. It was all about basic data modeling, etc. Having said this, the reason I keep mentioning their convention for temporal intervals is (1) it is being considered for ANSI ISO standards, thanks largely to the popularity of MySQL (2) it is language independent (3) it sorts properly with the rest of the ISO 8601 display format. In short, they are a good choice at the current time for conforming to international standards.
You probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
Since Microsoft has not implemented the ANSI/ISO standard interval data types in its temporal model, we have to do a "repair job" instead. If you have a better suggestion. I'd love to hear and put it in my books.
REALLY inappropriate thing to say, Joe.
I also hope that the attempt to sanction such non-sense as what they do in MySQL totally fails instantly. ""00" for day or month is inappropriate because there is no such thing as a 0 for day of the month or a month. The use of blanks would make it sort just fine if you were into sorting the mistake dates as character based values. Further, adding "00" to the standard would further perpetuate peoples' mistakes of storing such a thing in a table.
My big concern with these new de facto range dates is "how specifically do they join"? (*) If they are in a table, they can be joined on. So does a column containing 2018-05-00 joined to another date column match every day in May or only another -00 "date"?
(*) Leaving aside for now their possible(?) violation of 1NF, in that a date of 2018-01-00 could really be considered a list of all dates in Jan(?). Perhaps so, depending on how it joins.
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".
May 31, 2018 at 12:51 pm
DesNorton - Wednesday, May 30, 2018 4:01 AMWhen looking up the month names, you could also use the CHOOSE functionReplace this large CASE statement
case c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
There 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,with ...
CHOOSE(c.m, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') as [Period]
There is no such function in SQL as CHOOSE. Why would anyone want to code like hillbilly 🙁 ?
Please post DDL and follow ANSI/ISO standards when asking for help.
May 31, 2018 at 12:54 pm
jcelko212 32090 - Thursday, May 31, 2018 11:17 AMLynn Pettis - Tuesday, May 29, 2018 3:37 PMjcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AMMr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
I suppose in the interest of full disclosure, I did work for the MySQL people and produced some website material. It was all about basic data modeling, etc. Having said this, the reason I keep mentioning their convention for temporal intervals is (1) it is being considered for ANSI ISO standards, thanks largely to the popularity of MySQL (2) it is language independent (3) it sorts properly with the rest of the ISO 8601 display format. In short, they are a good choice at the current time for conforming to international standards.
You probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
Since Microsoft has not implemented the ANSI/ISO standard interval data types in its temporal model, we have to do a "repair job" instead. If you have a better suggestion. I'd love to hear and put it in my books.
REALLY inappropriate thing to say, Joe.
I also hope that the attempt to sanction such non-sense as what they do in MySQL totally fails instantly. ""00" for day or month is inappropriate because there is no such thing as a 0 for day of the month or a month. The use of blanks would make it sort just fine if you were into sorting the mistake dates as character based values. Further, adding "00" to the standard would further perpetuate peoples' mistakes of storing such a thing in a table.
My big concern with these new de facto range dates is "how specifically do they join"? (*) In they're in a table, they could get joined on. So does 2018-05-00 joined to another date column match every day in May or only another -00 day?
(*) Leaving aside for now their possible(?) violation of 1NF, in that a date of 2018-01-00 could really be considered a list of all dates in Jan(?). Perhaps so, depending on how it joins.
I suppose those people in favor of such "standards" could store their dates and times as separate integers like was done in some msdb tables. Now...you can't do date math with it and it's a complete pain to work with, but they would sort properly. If it starts with a year, you won't have to worry about leading zeros, but you would have to LPAD the month and day with zeros to make sure they're all 8 digits long.
Personally, I won't use that convention. The datetime data type is just too efficient and it doesn't suffer from the limitations of the datetime2 type.
May 31, 2018 at 1:10 pm
ddly_ann - Monday, May 28, 2018 7:47 AM
Heh... and besides, it violates the very standards that Joe busts everyone chops about. "00" is not a valid representation for either day or month in the ISO 8601 standard on the subject of such temporal notations. It would be better to use blanks but that would also perpetuate the mistake of storing temporal display data in character based columns instead of using the proper data type.
Please don't misquote me. All I said was is that the MySQL formats for intervals uses "00" and, and that when it is in display format – – repeat that display format – – it will sort correctly. I've also said again and again that it sort correctly, if you assume a hierarchical sort order for temporal data. I hope this eventually gets through the standards committees because I find it be really convenient and understandable convention Then I said it is up for consideration. Just like I said that *= should anticipate being changed to LEFT INNER JOIN , or that when BIT became a nullable data type (numeric), that you should add a check constraint to make sure that none of the existing columns with that unfortunate data type were actually nullable.
Until Microsoft gets interval data types, we'll have to use a lookup table that has the name of the interval and its definition, given by start and end dates. If you really can't think in terms of sets and lookup tables, then I suppose you can go ahead and use the highly proprietary temporal functions in this particular product. Of course, you'll never port your code anybody else and when the standards do change, you'll be screwed.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 31, 2018 at 1:23 pm
jcelko212 32090 - Thursday, May 31, 2018 12:51 PMThere is no such function in SQL as CHOOSE. Why would anyone want to code like hillbilly 🙁 ?
Try running this
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Dipstick' ) AS Result;
May 31, 2018 at 2:02 pm
jcelko212 32090 - Thursday, May 31, 2018 12:51 PMThere is no such function in SQL as CHOOSE. Why would anyone want to code like hillbilly 🙁 ?
You should really check before you make such statements; CHOOSE has been a function in T-SQL since SQl Server 2012. Like i said in another topic, SQL Server Central is a SQL Server website; just because a function isn't part of standard SQL doesn't mean it's not available in T-SQL (and vice versa). I hope you don't tell users here to use a double pipe (||) to concatenate their strings as the + operator for concatenating "doesn't exist in SQL". 🙂
For your reference: Logical Functions - Choose (Transact-SQL).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 31, 2018 at 2:43 pm
Lynn Pettis - Tuesday, May 29, 2018 3:37 PMjcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AMMr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
I suppose in the interest of full disclosure, I did work for the MySQL people and produced some website material. It was all about basic data modeling, etc. Having said this, the reason I keep mentioning their convention for temporal intervals is (1) it is being considered for ANSI ISO standards, thanks largely to the popularity of MySQL (2) it is language independent (3) it sorts properly with the rest of the ISO 8601 display format. In short, they are a good choice at the current time for conforming to international standards.
You probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
Since Microsoft has not implemented the ANSI/ISO standard interval data types in its temporal model, we have to do a "repair job" instead. If you have a better suggestion. I'd love to hear and put it in my books.
REALLY inappropriate thing to say, Joe.
I also hope that the attempt to sanction such non-sense as what they do in MySQL totally fails instantly. ""00" for day or month is inappropriate because there is no such thing as a 0 for day of the month or a month. The use of blanks would make it sort just fine if you were into sorting the mistake dates as character based values. Further, adding "00" to the standard would further perpetuate peoples' mistakes of storing such a thing in a table.
The sort of characterization by Joe is along the lines of the Churchill quote, "the americans will do the right thing, after they've done everything else" or something along those lines. I don't think his remark warranted the threadizen gang op you are inciting.
2 cents.
May 31, 2018 at 2:48 pm
jcelko212 32090 - Thursday, May 31, 2018 12:51 PMDesNorton - Wednesday, May 30, 2018 4:01 AMWhen looking up the month names, you could also use the CHOOSE functionReplace this large CASE statement
case c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
There 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,with ...
CHOOSE(c.m, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') as [Period]There is no such function in SQL as CHOOSE. Why would anyone want to code like hillbilly 🙁 ?
So you are saying that rural people who live in Appalachia or the Ozarks are inferior coders? Between the French and hillbillys in this thread you have demonstrated truly sad levels of intolerance for others. You berate others constantly for coding like they are still in the 70s, maybe you should bring your thinking of other people out of the 70s.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2018 at 2:58 pm
Sean Lange - Thursday, May 31, 2018 2:48 PMjcelko212 32090 - Thursday, May 31, 2018 12:51 PMDesNorton - Wednesday, May 30, 2018 4:01 AMWhen looking up the month names, you could also use the CHOOSE functionReplace this large CASE statement
case c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
There 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,with ...
CHOOSE(c.m, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') as [Period]There is no such function in SQL as CHOOSE. Why would anyone want to code like hillbilly 🙁 ?
So you are saying that rural people who live in Appalachia or the Ozarks are inferior coders? Between the French and hillbillys in this thread you have demonstrated truly sad levels of intolerance for others. You berate others constantly for coding like they are still in the 70s, maybe you should bring your thinking of other people out of the 70s.
Are you calling me a hillbilly because of where I live? Comment reported!
May 31, 2018 at 4:30 pm
jcelko212 32090 - Thursday, May 31, 2018 11:17 AMLynn Pettis - Tuesday, May 29, 2018 3:37 PMjcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AMMr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
I suppose in the interest of full disclosure, I did work for the MySQL people and produced some website material. It was all about basic data modeling, etc. Having said this, the reason I keep mentioning their convention for temporal intervals is (1) it is being considered for ANSI ISO standards, thanks largely to the popularity of MySQL (2) it is language independent (3) it sorts properly with the rest of the ISO 8601 display format. In short, they are a good choice at the current time for conforming to international standards.
You probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
Since Microsoft has not implemented the ANSI/ISO standard interval data types in its temporal model, we have to do a "repair job" instead. If you have a better suggestion. I'd love to hear and put it in my books.
REALLY inappropriate thing to say, Joe.
I also hope that the attempt to sanction such non-sense as what they do in MySQL totally fails instantly. ""00" for day or month is inappropriate because there is no such thing as a 0 for day of the month or a month. The use of blanks would make it sort just fine if you were into sorting the mistake dates as character based values. Further, adding "00" to the standard would further perpetuate peoples' mistakes of storing such a thing in a table.
The sort of characterization by Joe is along the lines of the Churchill quote, "the americans will do the right thing, after they've done everything else" or something along those lines. I don't think his remark warranted the threadizen gang op you are inciting.
2 cents.
Justifying one inappropriate comment with another doesn't make the first one appropriate. There's also quite a bit of doubt that Churchill ever said such a thing although Churchill may have had the poor taste to agree with it.
https://quoteinvestigator.com/2012/11/11/exhaust-alternatives/
https://richardlangworth.com/americans
As for inciting Joe to do anything, not possible. Joe, if nothing else, is totally consistent and cannot be swayed by anyone.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2018 at 4:45 pm
jcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AM
Heh... and besides, it violates the very standards that Joe busts everyone chops about. "00" is not a valid representation for either day or month in the ISO 8601 standard on the subject of such temporal notations. It would be better to use blanks but that would also perpetuate the mistake of storing temporal display data in character based columns instead of using the proper data type.
Please don't misquote me. All I said was is that the MySQL formats for intervals uses "00" and, and that when it is in display format – – repeat that display format – – it will sort correctly. I've also said again and again that it sort correctly, if you assume a hierarchical sort order for temporal data. I hope this eventually gets through the standards committees because I find it be really convenient and understandable convention Then I said it is up for consideration. Just like I said that *= should anticipate being changed to LEFT INNER JOIN , or that when BIT became a nullable data type (numeric), that you should add a check constraint to make sure that none of the existing columns with that unfortunate data type were actually nullable.
Until Microsoft gets interval data types, we'll have to use a lookup table that has the name of the interval and its definition, given by start and end dates. If you really can't think in terms of sets and lookup tables, then I suppose you can go ahead and use the highly proprietary temporal functions in this particular product. Of course, you'll never port your code anybody else and when the standards do change, you'll be screwed.
Look back in this thread and tell me where I've misquoted you. 😉 You said that you like the "00" notation and I said that's a violation of the ISO 8601 standard the you brow beat people with. I also stated that I don't like the "00" notation because the simple use of appropriately placed blanks are also sortable if you're prone to sorting display values and certainly shouldn't be stored. You never said that you'd store such an atrocity. I'm just making sure that storing such a thing is wrong just like storing any temporal value as a character based column is wrong. I also said I don't like it because it will further entice people into doing so and that's the reason why I hope that any attempt to incorporate it into ISO 8601 fails miserably.
As for MS not having interval data types, I totally agree with you. They've even taken a giant step backwards by making it impossible to do direct date math with the DATE, TIME, DATETIME2(), and the other relatively "new" datatypes they introduced about a decade ago. At least they didn't screw up the ability to do such things with the DATETIME datatype. For example, if we want to know the date and time it will be 22 hours, 23 minutes, and 58 seconds from now, the DATETIME datatype does actually follow the ISO 8601 standard of StartDateTime + Interval = EndDateTime even if the obvious format for Interval (or duration) is limited to a mere 24 hours. For anything more than 23:59:59.997, you have to know the trick of either using the decimal equivalent for the Interval or know that the datetime serial number of the 19000101 is zero.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2018 at 10:06 pm
jcelko212 32090 - Thursday, May 31, 2018 12:51 PMDesNorton - Wednesday, May 30, 2018 4:01 AMWhen looking up the month names, you could also use the CHOOSE functionReplace this large CASE statement
case c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
There 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,with ...
CHOOSE(c.m, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') as [Period]There is no such function in SQL as CHOOSE. Why would anyone want to code like hillbilly 🙁 ?
Perhaps you should open SSMS and try it.
I don't care whether it's in your precious standards or not. We work with MS T-SQL, and CHOOSE is one of the tools available to us.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply