Home Forums SQL Server 2017 SQL Server 2017 - Development Need Help to convert DATETIME result into DATE on SQL Server RE: Need Help to convert DATETIME result into DATE on SQL Server
July 19, 2018 at 5:42 pm
Jeff Moden - Wednesday, July 18, 2018 3:24 PMjcelko212 32090 - Wednesday, July 18, 2018 2:17 PMmeichmann - Wednesday, July 18, 2018 10:39 AMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PMHe probably helped set the standards for the Gregorian calendar since he boasts about setting everything else up....
No, I worked on the ANSI/ISO dirt standard, which helped in the Stone Age 🙂
However, we did have some communication with the people working on the ISO 8601 temporal standards. This is why we only allow the "yyyy-mm-dd" display format in the standards. Otherwise, we wind up with the mess you see in SQL Server, Oracle, and lots of other SQL products that allows all kinds of local dialect display formats, which makes information interchange difficult, ambiguous, and occasionally impossible.
Ah... there you go again. The "yyyy-mm-dd" display format is actually an acceptable alternate according to ISO 8601 and, because it's an alternate, that means there's a primary and that means it's NOT the ONLY standard. The primary display format is "yyyymmdd" according to ISO 8601.
You haven't spent a lot of your time working with standards, have you? I can't remember who it was first said "there ae sooo many to pick from"(Dyson?)
We deliberately decided on the dashed format from the ISO 8601 when you look a a string of digits like a string of digits 20180715, how do you know it's not an integer? But when you look at a string2018-07-15 you are certain it is a date. The one thing I didn't like is it when we got to timestamps, we use a space between the date and the time fields; you can use an uppercase T instead and have a string of solid characters. This avoids the "whitespace problem" or you're not sure if you wanted a space, a tab, a carriage return or a new line.
There are other things in the SQL standards that I don't like, but I think we got this one right.
Ok, then... which standard are you looking at? I'm referring to ISO 8601, specifically paragraph 4.1.2.2. What are you actually referring to when you cite ISO 8601? For educational purposes, here's the copy'n'paste snippet of paragraph 4.1.2.2 from ISO 8601.
4.1.2.2 Complete representations
When the application identifies the need for a complete representation of a calendar date, it shall be one
of the numeric expressions as follows, where [YYYY] represents a calendar year, [MM] the ordinal
number of a calendar month within the calendar year, and [DD] the ordinal number of a calendar day
within the calendar month.Basic format: YYYYMMDD Example: 19850412
Extended format: YYYY-MM-DD Example: 1985-04-12
It looks to me like there's not only more than one allowed display format but it also looks like the dashed format is secondary. It also looks like they don't give a hoot about whether it looks like an integer or not. 😉
Based on what you said above...
We deliberately decided on the dashed format from the ISO 8601
... it sounds like you're talking about a standard other than ISO 8601 that borrowed snippets from ISO 8601 but you haven't cited what that standard is. Please identify the "standard" that you're speaking of if it's other than ISO 8601 because I might actually agree then. 😀 If it IS ISO 8601 that you're actually talking about then, according to paragraph 4.1.2.2, you're dead wrong when you say that the dashed format is the only display format allowed.
How' bout it, Joe? Which standard are you actually referring to ?
--Jeff Moden
Change is inevitable... Change for the better is not.