• jcelko212 32090 - Tuesday, January 23, 2018 5:54 PM

    ScottPletcher - Tuesday, January 23, 2018 2:24 PM

    Does YYYY-MM-00 -- not to mention YYYY-00-00 -- raise 1NF / atomicity issues?  Does the column represent an atomic date or a range of dates?  Is the range somehow deemed atomic?

    Also, is this now valid:
    WHERE column_date >= '2018-01-00' AND column_date < '2018-02-00'
    ?
    What specifically does it mean?

    These are called temporal interval datatypes, to go with date, time and timestamp datatypes. They represent an interval, not an atomic date. I mean for example a DATE datatype is really a shorthand for the range from 00:00:00 to 23:59:59.99999.. on that date.

    Right now you should get a syntax error, because the ANSI/ISO standards don't support this notation. I'm also not sure if any of the proposals specify how to handle BETWEEN and comparisons using this notation. I would assume that the same as any other interval data type or behaves the way that date does currently.

    I honestly don't know what the status is inside ISO – 8601; I never worked with that committee.

    In this scenario it appears to me that you now have a data type of what a procedure language might classify as TIME [ ]  ... That is a array of of type TIME, consisting of all possible time values. What if I only want the first half of those? What is the size of my array? I like that SQL Server considers time to be an infinitesimal point, and represents it in memory as such to the best of its ability.  Keeps things simple as I see it.

    ----------------------------------------------------