get around invalid date

  • ScottPletcher

    SSC Guru

    Points: 98401

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • drew.allen

    SSC Guru

    Points: 76712

    jcelko212 32090 wrote:

    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

  • x

    SSC-Insane

    Points: 23548

    -- 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

     

  • Luis Cazares

    SSC Guru

    Points: 183587

    Just change this line in your code.

    set @testchar = convert(datetime,'2019-10-12')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • x

    SSC-Insane

    Points: 23548

    Luis Cazares wrote:

    Just change this line in your code.

    set @testchar = convert(datetime,'2019-10-12')

    "And if my suggestions are broken, are so bad, why do the recent DATE and DATETIME2(n) data types default to it?"

    I was interested in that sentence.

  • jcelko212 32090

    SSCrazy Eights

    Points: 8949

    >> 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. 

  • ScottPletcher

    SSC Guru

    Points: 98401

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • x

    SSC-Insane

    Points: 23548

    ScottPletcher wrote:

    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?

     

    • This reply was modified 6 months ago by  x.
  • x

    SSC-Insane

    Points: 23548

    jcelko212 32090 wrote:

    >> 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.

     

  • Jeffrey Williams

    SSC Guru

    Points: 88355

    x wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • x

    SSC-Insane

    Points: 23548

    +1 informative, thanks Jeffrey!

     

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply