October 10, 2019 at 5:27 pm
And if my suggestions are broken, are so bad, why do the recent DATE and DATETIME2(n) data types default to it?
Default to what exactly? We know they're not stored in YYYY-MM-DD format. You mean entry format? That's the same for any of the date* data types, not just the recent [relatively recent: date's been around for a decade in SQL Server] ones. In SQL Server, the default entry format is affected by the date and language settings. That's just a fact, whether anyone prefers it that way or not.
Given that, the only truly safe formats for input are 'YYYYMMDD' and 'YYYY-MM-DDThh:mm:ss' (note that the seconds are required). The former is vastly easier than the latter, so it's preferred.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 10, 2019 at 6:44 pm
And if my suggestions are broken, are so bad, why do the recent DATE and DATETIME2(n) data types default to it?
You're confusing the data layer with the presentation layer. SQL Server dates are not stored in any format. The display is left to the presentation layer. Perhaps you should take a course in database design. 😉
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2019 at 2:20 pm
-- I was just curious on this one.
declare @test datetime2(7)
declare @testchar varchar(30)
declare @testlang sysname
set @test = '2019-10-12 15:00'
-- Without giving the third parameter in the included convert, i''m going to SUGGEST
-- that this the default format that comes from within SQL server. Obviously its stored
-- in a different format consisting of a string of bytes which are interpretted by SQL Server
-- to be two integers. Of course its also server side formatting, sorry Joe!
declare @ix int
set @ix = 0
while (@ix <= 33)
begin
set @testlang = (select name from sys.syslanguages where langid = @ix)
set language @testlang
set @testchar = convert(varchar(30),@test)
print @testchar
set @ix = @ix + 1
end
What other "locale" like settings can be varied? Just poking around that sall.
I'm curious why yyyy-mm-dd is considered ambiguous where yyyymmdd isn't, ie., why do dashes change the nature of the interpretation? Is that discussed somewhere? Heh I'm not very informed on standards, that's Celko's job LOL
October 11, 2019 at 2:29 pm
Just change this line in your code.
set @testchar = convert(datetime,'2019-10-12')
October 11, 2019 at 4:58 pm
>> I'm curious why yyyy-mm-dd is considered ambiguous where yyyymmdd isn't, ie., why do dashes change the nature of the interpretation? <<
The problem with the all digits string is it can be mistaken for an integer. The machine can parse these both the same way. However, people do not. Picking out the year, month or day field is much easier with punctuation. One of the standard test for readability of data is to put the encodings in a column, as it would appear on a printout. Your test subject (usually an undergrad student in my psychology class whose failing and needs the extra credit) is asked the following simple tasks:
Sort the list in chronological order
find an incorrect date in the list
find a missing date in the list
The dashes improve readability greatly (it's been too many years but I think it was like a 40% improvement). Most of this work was done at the University of Maryland or if it wasn't done there it was collected from other universities at the University of Maryland. When I was with AIRMICS at Georgia Tech, they were on contract with us. I spent a couple of years compiling human factors research from various sources and used to write columns in the trade press on the very new topic, software engineering.
>> Is that discussed somewhere? Heh, I'm not very informed on standards, that's Celko's job LOL. <<
Readability and formatting of code was a big topic in the 1970s. I'll be honest after all these decades, I don't think I have any of my old research material or presentations. But hey! That's why we have a Google 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
October 11, 2019 at 5:13 pm
The problem with the all digits string is it can be mistaken for an integer.
100% false. Date literals are strings, or delimited in some other way to distinguish them from int (e.g. Access uses #s (for some unfathomable reason); DB2 uses quotes, like SQL Server).
At any rate, 2019-10-11 would also be an integer: 1998 (2019 minus 21). Try SELECT 2019-10-11 in whatever dbms you want and I figure close to 100% of the time you'll get a result of 1998 and not 2019-10-11.
why do dashes change the nature of the interpretation?
I believe to be consistent with local practice. Otherwise it would be terribly confusing for people to enter SQL dates. If you're used to seeing "2019-13-10 08:00AM" it would be confusing for you to have to enter it as "2019-10-13 08:00AM", just as it would confusing for Americans to have to enter "2019-13-10".
At any rate, it is a fact that SQL Server will, depending on specific settings, sometimes interpret a datetime entered in YYYY-NN-NN format as YYYY-DD-MM and not YYYY-MM-DD. Therefore, a date format with dashes is undeniably ambiguous.
Thus, for date literals, you should always use YYYYMMDD, period. Yeah, it's a tiny bit harder to read/process, at least initially, but you get used to it. And that's vastly better than getting invalid results because of an incorrect date conversion/interpretation.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 11, 2019 at 6:34 pm
The problem with the all digits string is it can be mistaken for an integer.
100% false. Date literals are strings, or delimited in some other way to distinguish them from int (e.g. Access uses #s (for some unfathomable reason); DB2 uses quotes, like SQL Server).
At any rate, 2019-10-11 would also be an integer: 1998 (2019 minus 21). Try SELECT 2019-10-11 in whatever dbms you want and I figure close to 100% of the time you'll get a result of 1998 and not 2019-10-11.
2019-10-11 with quotes is hard to mistake as an integer. 2019-10-11 without quotes isn't an integer either, its an expression. I'll grant that it evaluates to an integer, but its not an integer nor is its first stop during translation, its still an expression, once it gets examined lexically.
I know that's picky, but try forgetting quotes the next time you enter a date literal and see how picky the computer gets.
why do dashes change the nature of the interpretation?
I believe to be consistent with local practice. Otherwise it would be terribly confusing for people to enter SQL dates. If you're used to seeing "2019-13-10 08:00AM" it would be confusing for you to have to enter it as "2019-10-13 08:00AM", just as it would confusing for Americans to have to enter "2019-13-10".
At any rate, it is a fact that SQL Server will, depending on specific settings, sometimes interpret a datetime entered in YYYY-NN-NN format as YYYY-DD-MM and not YYYY-MM-DD. Therefore, a date format with dashes is undeniably ambiguous.
Thus, for date literals, you should always use YYYYMMDD, period. Yeah, it's a tiny bit harder to read/process, at least initially, but you get used to it. And that's vastly better than getting invalid results because of an incorrect date conversion/interpretation.
I'm interested in which setting would change how a quoted date with dashes gets interpreted. I just checked and my little language thing didn't do it. Which setting changes this?
October 11, 2019 at 6:48 pm
>> I'm curious why yyyy-mm-dd is considered ambiguous where yyyymmdd isn't, ie., why do dashes change the nature of the interpretation? <<
The problem with the all digits string is it can be mistaken for an integer. The machine can parse these both the same way. However, people do not. Picking out the year, month or day field is much easier with punctuation. One of the standard test for readability of data is to put the encodings in a column, as it would appear on a printout. Your test subject (usually an undergrad student in my psychology class whose failing and needs the extra credit) is asked the following simple tasks:
Sort the list in chronological order
find an incorrect date in the list
find a missing date in the list
The dashes improve readability greatly (it's been too many years but I think it was like a 40% improvement). Most of this work was done at the University of Maryland or if it wasn't done there it was collected from other universities at the University of Maryland. When I was with AIRMICS at Georgia Tech, they were on contract with us. I spent a couple of years compiling human factors research from various sources and used to write columns in the trade press on the very new topic, software engineering.
>> Is that discussed somewhere? Heh, I'm not very informed on standards, that's Celko's job LOL. <<
Readability and formatting of code was a big topic in the 1970s. I'll be honest after all these decades, I don't think I have any of my old research material or presentations. But hey! That's why we have a Google 🙂
That makes sense. Heck, its with a bit of sadness that I note you imply readability and formatting is no longer a big topic 🙁
I've been revisiting some older textbooks (especially the ones with updates) and one of my favorites is the old "psychology of computer programming" and although subjective, it really reinforces what you're saying, maybe that's why I'm objecting a bit to some of the posts here. I mean that doesn't mean I'm going to quit using punched cards, but I can see where you're coming from, and I'm hoping Scott will clarify so that I at least can understand both why this thread is having difficulty agreeing on date representation for humans AND computers and secondly whether there is a real reason my using 'YYYY-MM-DD' as a date literal would get me in trouble for SQL Server.
October 12, 2019 at 4:12 pm
I've been revisiting some older textbooks (especially the ones with updates) and one of my favorites is the old "psychology of computer programming" and although subjective, it really reinforces what you're saying, maybe that's why I'm objecting a bit to some of the posts here. I mean that doesn't mean I'm going to quit using punched cards, but I can see where you're coming from, and I'm hoping Scott will clarify so that I at least can understand both why this thread is having difficulty agreeing on date representation for humans AND computers and secondly whether there is a real reason my using 'YYYY-MM-DD' as a date literal would get me in trouble for SQL Server.
The problem with YYYY-MM-DD is related to the DATETIME data type only. For DATE and DATETIME2 data types it will be interpreted correctly.
To see the problem:
Set language 'British English';
Go
Select cast('2019-13-10' As datetime);
Select cast('2019-13-10' As date);
Select cast('2019-13-10' As datetime2(0));
Set language 'English';
Go
Select cast('2019-13-10' As datetime);
Select cast('2019-13-10' As date);
Select cast('2019-13-10' As datetime2(0));
For British English - the format for datetime is interpreted as YYYY-DD-MM but for DATE and DATETIME2 it will be interpreted as YYYY-MM-DD. For English it will be interpreted as YYYY-MM-DD for all data types...
The format YYYYMMDD will be interpreted correctly for all data types regardless of language or dateformat settings in SQL Server.
If you are using the DATE/DATETIME2 data types then you won't have any issues with YYYY-MM-DD, but if your system has to work with other languages then that format could present issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2019 at 8:22 pm
+1 informative, thanks Jeffrey!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply