jcelko212 32090 - Saturday, February 3, 2018 9:11 PM
Considering your normal responses (your most recent above being no exception) to people, Joe, you have ZERO room to give this type of advice to anyone until you first straighten yourself out so that you don't also post apparently ignorant responses.
Not only did you post pictures, but you even seem to know that standard ANSI/ISO Standard SQL only allows for dates to be formatted as "yyyy-mm-dd" strings.
...{snip}...
This is based on the ISO 8601 temporal display standards. Anybody in IT should know what because the second most popular standard after the metric system.
You used the word "ignorance" in your response to the OP. Perhaps you should work on your own ignorance a bit because your statement above is totally false. The basic acceptable format in the standards you've cited is YYYYMMDD and they list YYYY-MM-DD as an acceptable extension. The following is a quote from the ISO/WD 8601-1 standard (dated 2016-02-16) that you keep citing and, apparently, haven't read.
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
You also speak of the ISO week format...
This standard includes the ISO weekly date format,.: "yyyyW[1-5][0-9]-[1-7]" the first four digits are the year, the next token is a W, followed by the number of the week within the year (1 to 52 or 53) another piece of punctuation the –, and the day of the week (Monday =1).
While that's correct, it certainly isn't the only format (implied by your use of the word "the"). The following is from the ISO/WD 8601-1 standard, which allows for 3 other formats as well.
4.1.4.2 Complete representations
When the application identifies the need for a complete representation of a week date, it shall be one of the alphanumeric expressions as follows, where [YYYY] represents a calendar year, [W] is the week designator, [ww] represents the ordinal number of a calendar week within the year, and [D] represents the ordinal number of a calendar day within the calendar week.
Basic format: YYYYWwwD Example: 1985W155
Extended format: YYYY-Www-D Example: 1985-W15-54.1.4.3 Representations with reduced accuracy
If the degree of accuracy required permits, one digit may be omitted from the representation in 4.1.4.2.
A specific week
Basic format:YYYYWww Example: 1985W15
Extended format: YYYY-Www Example: 1985-W15
Unfortunately, such a format isn't normally acceptable for human consumption because humans cannot quickly resolve such representations to a Calendar Date never mind the fact that the first week of an ISO year may contain days from the previous year and the last week of an ISO year may contain days from the next year. Except for extremely esoteric reports and date stamps on cans of cat food and the like (which is also stupid because most consumers can't interpret what it means), the ISO week format should generally and usually be avoided for reporting purposes. Reports intended for human consumption should use one of the two acceptable ISO formats for the date of the beginning of an ISO week, instead and, contrary to popular belief, it's actually ok to customize the date formats for the intended audience. It's just not appropriate to store any formatted dates in the source data tables of a data tables.
Last but not least, standards are only good if the manufacturers of things like relational databases actually follow them. For example, the highly touted ISO standard of YYYY-MM-DD totally falls apart if the default language doesn't actually support it. For example, if the default language for an SQL Server installation is "French", the format silently changes to YYYY-DD-MM. That means that the Extended ISO format of allowing dashes to separate the date parts provides insitu and esoteric danger and should probably be avoided altogether.
SET NOCOUNT ON
;
PRINT '===== YYYYMMDD is interpreted correctly in SQL Server in the following cases.';
SET LANGUAGE english;
SELECT CONVERT(DATETIME,'20180203');
GO
SET LANGUAGE french;
SELECT CONVERT(DATETIME,'20180203');
GO
PRINT '===== YYYYMMDD is NOT interpreted correctly in SQL Server in the following cases.';
SET LANGUAGE english;
SELECT CONVERT(DATETIME,'2018-02-03');
GO
SET LANGUAGE french;
SELECT CONVERT(DATETIME,'2018-02-03');
GO
Results from above...
===== YYYYMMDD is interpreted correctly in SQL Server in the following cases.
Changed language setting to us_english.
-----------------------
2018-02-03 00:00:00.000
Le paramètre de langue est passé à Français.
-----------------------
2018-02-03 00:00:00.000
===== YYYYMMDD is NOT interpreted correctly in SQL Server in at least one case.
Changed language setting to us_english.
-----------------------
2018-02-03 00:00:00.000
Le paramètre de langue est passé à Français.
-----------------------
2018-[highlight="Yellow"]03-02[/highlight] 00:00:00.000
--Jeff Moden
Change is inevitable... Change for the better is not.