Need Help to convert DATETIME result into DATE on SQL Server

  • Nexana - Thursday, July 19, 2018 9:44 AM

    Jeff Moden - Thursday, July 19, 2018 8:03 AM

    Nexana - Wednesday, July 18, 2018 8:23 PM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

    1753 is the year when the Gregorian calendar started. Today, we use the Common Era calendar. It looks very much like the Gregorian calendar, without involving the Christian religion and the pope. It also starts at the year 0001 (there is no year zero) and refers to things being either "before the common era" BCE and just "common era" CE. There's a bunch of other conventions using negative numbers for the BCE years, but I don't find that's really much of a problem for business applications. In fact, the only time ever seen it use was when I worked at the Getty Museum briefly.

    I'm kind of holding my breath that may be the Edwards calendar will be adopted sometime. This one has four identical quarters of three months, the months are 30, 30 and 31 days long so they start on Monday, Wednesday and Friday within each quarter and the year is completely invariant. Well, not really. We need to add a New Year's Day (which can be shown as an inter-calendrical day zero) and a leap your day every four years in the middle of the year. I think I stand a better chance of getting the metric system in the United States and this,tho. 🙁

    Good info, Joe.  Thanks. 

    I'm curious as to how this will affect dates prior to 1753 mentioned in history books.

    @jeff;  Do you have any idea why this logic is not loading up to the end date? I still need to use DATE data type as the start date will be '01-01-0001' 

    SET DATEFIRST 7;

    DECLARE @CurrentDate DATE = '12-01-9999'
    DECLARE @EndDate DATE = '12-31-9999'

    CREATE TABLE #Date ([DATE] DATE);

    WHILE @CurrentDate < @EndDate
        BEGIN
             INSERT INTO #Date
             SELECT @CurrentDate AS Date

             SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
        END;

      SELECT MAX(DATE) AS DATE FROM #Date

    Yes.  Look at your WHILE statement... it says to stop when @CurrentDate < @EndDate.  You also have to understand that the code that increments the WHILE loop counter (@CurrentDate), is trying to create a date that is beyond the range of dates in SQL Server.  It causes an error that isn't a showstopper and so the code "loops forever". 

    Even if it worked correctly, I wouldn't use WHILE loops for this type of thing.  Do you know what a Tally or Numbers table is?

    I was reading your article about Tally and it's very helpful. However, since ther are already numerous logics in between the loop; can you suggest a quick fix? then after; I will look into implementing the Tally concept instead of WHILE. Thanks as always.

    Lynn Petits hit the nail on the head with his code.  The answer for such things should never be a loop even for a small number of rows because you're just perpetuating a bad habit. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Wednesday, July 18, 2018 12:11 PM

    sgmunson - Tuesday, July 17, 2018 7:34 AM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

    1753 is the year when the Gregorian calendar started. Today, we use the Common Era calendar. It looks very much like the Gregorian calendar, without involving the Christian religion and the pope. It also starts at the year 0001 (there is no year zero) and refers to things being either "before the common era" BCE and just "common era" CE. There's a bunch of other conventions using negative numbers for the BCE years, but I don't find that's really much of a problem for business applications. In fact, the only time ever seen it use was when I worked at the Getty Museum briefly.

    I'm kind of holding my breath that may be the Edwards calendar will be adopted sometime. This one has four identical quarters of three months, the months are 30, 30 and 31 days long so they start on Monday, Wednesday and Friday within each quarter and the year is completely invariant. Well, not really. We need to add a New Year's Day (which can be shown as an inter-calendrical day zero) and a leap your day every four years in the middle of the year. I think I stand a better chance of getting the metric system in the United States and this,tho. 🙁

    Good info, Joe.  Thanks. 

    I'm curious as to how this will affect dates prior to 1753 mentioned in history books.

    Not much that can really be done with much of that.   Relatively little is known about what calendars were actually in use in many parts of the world at various times, and even educated guesses get out of whack rather quickly just trying to accommodate Western Europe.   It seems (to me, anyway) unlikely that new information will come to light that helps much, as so much of that part of the world operated on so many different calendars, and there was so much in the way of "exceptions", that stringing together a viable timeline at any point prior to 1/1/1753 is most likely in the category of at the very least impractical, if not impossible, ... unless someone manages to invent a view only time machine...

    Yep... understood.  I just want to know if I need to relearn a whole bunch of childhood rhymes like "Columbus sailed the ocean Blue in fourteen hundred and ninety-two". 😀

    Then perhaps you are familiar with "Columbus sailed the deep blue sea, in fourteen-hundred and ninety-three" ?   Probably a true statement, but not the standard one you mentioned.   I can't recall where I heard that "alternative"...  or under what circumstances I heard it...  shame really... would have loved to remember that given this topic...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Jeff Moden - Wednesday, July 18, 2018 8:10 PM

    jcelko212 32090 - Wednesday, July 18, 2018 7:56 PM

    Jeff Moden - Wednesday, July 18, 2018 3:24 PM

    jcelko212 32090 - Wednesday, July 18, 2018 2:17 PM

    meichmann - Wednesday, July 18, 2018 10:39 AM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

    He 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090 - Wednesday, July 18, 2018 7:56 PM

    Jeff Moden - Wednesday, July 18, 2018 3:24 PM

    jcelko212 32090 - Wednesday, July 18, 2018 2:17 PM

    meichmann - Wednesday, July 18, 2018 10:39 AM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

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

    Actually Joe, you cant assume that a string that looks like a date is actually a date.  It could be a case number or a part number, or anything else that isn't a date.

  • Jeff Moden - Thursday, July 19, 2018 5:42 PM

    Jeff Moden - Wednesday, July 18, 2018 8:10 PM

    jcelko212 32090 - Wednesday, July 18, 2018 7:56 PM

    Jeff Moden - Wednesday, July 18, 2018 3:24 PM

    jcelko212 32090 - Wednesday, July 18, 2018 2:17 PM

    meichmann - Wednesday, July 18, 2018 10:39 AM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

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

    Damn... so typical of you, Joe.  You drop some bombs and then walk away.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Friday, July 20, 2018 8:10 AM

    Jeff Moden - Thursday, July 19, 2018 5:42 PM

    Jeff Moden - Wednesday, July 18, 2018 8:10 PM

    jcelko212 32090 - Wednesday, July 18, 2018 7:56 PM

    Jeff Moden - Wednesday, July 18, 2018 3:24 PM

    jcelko212 32090 - Wednesday, July 18, 2018 2:17 PM

    meichmann - Wednesday, July 18, 2018 10:39 AM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

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

    Damn... so typical of you, Joe.  You drop some bombs and then walk away.

    I have yet to see Joe admit when he is wrong.  Hoped this would have been that one time.

  • Lynn Pettis - Friday, July 20, 2018 8:20 AM

    Jeff Moden - Friday, July 20, 2018 8:10 AM

    Jeff Moden - Thursday, July 19, 2018 5:42 PM

    Jeff Moden - Wednesday, July 18, 2018 8:10 PM

    jcelko212 32090 - Wednesday, July 18, 2018 7:56 PM

    Jeff Moden - Wednesday, July 18, 2018 3:24 PM

    jcelko212 32090 - Wednesday, July 18, 2018 2:17 PM

    meichmann - Wednesday, July 18, 2018 10:39 AM

    Jeff Moden - Monday, July 16, 2018 3:58 PM

    jcelko212 32090 - Monday, July 16, 2018 2:29 PM

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

    Damn... so typical of you, Joe.  You drop some bombs and then walk away.

    I have yet to see Joe admit when he is wrong.  Hoped this would have been that one time.

    I don't know if he's wrong on this one or not.  That's what I'm trying to find out.  If he's only referring to ISO 8601 then, yes, he's wrong.  If, however and as his innuendo implies, that he's referring to a different standard that did borrow (as he suggests) from ISO 8601, then he may be correct.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I'm curious as to how this will affect dates prior to 1753 mentioned in history books.

    Dates prior to 1753 defined by different calendars.

    Using extrapolation of Grigorian calendar to that period of time is kinda lame excercise.

    Placement of those dates on the timeline will never be correct.

    So, it's a full's gold, like many other this great MS added to the product to please developers with limited education.

    Orthodoxal church still uses Julian calendar, so their Christmas Day iappears to be on 7 January, by our common calendar, but it's actually on 25 December, just according to another calendar.

    _____________
    Code for TallyGenerator

  • Sergiy - Monday, July 23, 2018 3:52 PM

    I'm curious as to how this will affect dates prior to 1753 mentioned in history books.

    Dates prior to 1753 defined by different calendars.Using extrapolation of Grigorian calendar to that period of time is kinda lame excercise.Placement of those dates on the timeline will never be correct.So, it's a full's gold, like many other this great MS added to the product to please developers with limited education.Orthodoxal church still uses Julian calendar, so their Christmas Day iappears to be on 7 January, by our common calendar, but it's actually on 25 December, just according to another calendar.

    Heh.... Thanks, Sergiy... the question was mostly directed at Celko because he seemed to be implying that it was all "ok" and was looking for what his response would be.  As he frequently does, he dropped a couple of bombs and then ran away.  I absolutely and have always agreed with what you posted above when it comes to dates prior to 1753.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Wednesday, July 18, 2018 12:11 PM

    Yep... understood.  I just want to know if I need to relearn a whole bunch of childhood rhymes like "Columbus sailed the ocean Blue in fourteen hundred and ninety-two". 😀

    I bet you have.
    It might be not so bad for Columbus, as the calendar which was used in Europe at the time was pretty close to the calendar used now, but actually none of the historical dates from those times can be legitimately placed into a SQL server DATE field.

    You know, the switch to Gregorian Calendar is so distant for Europeans (and non-existent for Americans), that they don't realise where is the issue.
    But we in our childhood, back in Sovet Union times, learnt to accept, that the anniversary of October Socialist Revolution is celebrated on 7th November.
    And if you look at the historical records you'll find out that count Leo Tolstoy was actually born on 28 August 1828. That was his birth-date throughout whole his life, with full accordance to the calendar used on the territory of Russian Empire at the time, but all contemporary sources refer to the date of 9 September as his birthday, which you cannot find in any of authentic biographical documents regarding this person.

    SQL Server uses Gregorian calendar for its date manipulations, and whatever pre-gregorian date you put in it will be either
    - incorrect, or
    - historically inaccurate.

    _____________
    Code for TallyGenerator

  • Sergiy - Monday, July 23, 2018 6:53 PM

    Jeff Moden - Wednesday, July 18, 2018 12:11 PM

    Yep... understood.  I just want to know if I need to relearn a whole bunch of childhood rhymes like "Columbus sailed the ocean Blue in fourteen hundred and ninety-two". 😀

    I bet you have.
    It might be not so bad for Columbus, as the calendar which was used in Europe at the time was pretty close to the calendar used now, but actually none of the historical dates from those times can be legitimately placed into a SQL server DATE field.

    You know, the switch to Gregorian Calendar is so distant for Europeans (and non-existent for Americans), that they don't realise where is the issue.
    But we in our childhood, back in Sovet Union times, learnt to accept, that the anniversary of October Socialist Revolution is celebrated on 7th November.
    And if you look at the historical records you'll find out that count Leo Tolstoy was actually born on 28 August 1828. That was his birth-date throughout whole his life, with full accordance to the calendar used on the territory of Russian Empire at the time, but all contemporary sources refer to the date of 9 September as his birthday, which you cannot find in any of authentic biographical documents regarding this person.

    SQL Server uses Gregorian calendar for its date manipulations, and whatever pre-gregorian date you put in it will be either
    - incorrect, or
    - historically inaccurate.

    Heh... the DATE datatype going back to 0001-01-01 cracks me up because it is an MS error to do so, as you state.  Remember the last couple of big glitches, at least one of which still exists in MS products?  The original date serial numbers in Excel had 0 being 1900-01-01 and that was great but they forgot 5th grade math for calculating which century years are leap years.  To this day, the 29th of February, 1900 continues to be a valid date in Excel unless you use the "fix", which was to start the calendar on the 1st of March, 1900.  Of course, that in turn reminds me of Y2K, which was also fun especially for money grubbers.  At that time, I was working for a small telephone company doing business in all CONUS states.  My biggest aggravation was filling out all the forms (they wouldn't allow substitutes for the forms and they were all different even within the same state) from the PUC and multiple other government agencies for all 48 states.  We had 0 modifications to make (I started writing code with 4 digit years back in 1980, which is when I first started writing commercial code for real) and had 0 problems.  It was also amazing how much junk mail and how many phone calls we got about how to protect ourselves from Y2K.  All they needed to get us started was some money, of course. 😀

    The post office made more money off me because of all the forms I had to mail out. 

    Getting back to the calendar thing with the DATE data type, I don't actually know of anyone that has used dates prior to 1900, never mind 1753, in SQL Server except as an "offset token" to do some other calculation for something after 1753 or as a non-sense date for dates that haven't happened.  Do you know of anyone that has used dates prior to 1753 in SQL Server (or any RDBMS) in earnest?

    Shifting gears a bit... MS also finally figured out how bad they messed up on the DATETIME2() data type but, instead of fixing it, they created DATEDIFF_BIG so that people could calculate periods at the millisecond level instead of just subtracting like you can with the DATETIME and SMALLDATETIME data types.

    Sorry... this is all coming out as a rant about MS.  They deserve the rant but I'm wasting my and your time talking about something they won't ever change.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 31 through 40 (of 40 total)

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