July 16, 2018 at 2:15 pm
Jeff Moden - Monday, July 16, 2018 1:59 PMsgmunson - Monday, July 16, 2018 7:10 AMJeff Moden - Friday, July 13, 2018 9:45 PMsgmunson - Tuesday, July 10, 2018 6:17 AMSQL Server simply will NOT work with any date value prior to January 1st, 1753. .Not sure what you're talking about. The DATE datatype works just fine with '0001-01-01'. In fact, it's the first boundary for DATE just like the first boundary for DATETIME is '1753-01-01'.
If you're saying that DATETIME won't work with '0001-01-01', then I totally agree but SQL Server WILL work with that date given the correct datatype.
As always, you educate me in new ways.... I was unaware that the DATE data type would support such, but the DATETIME does not, so I had once again made an assumption that turns out to have been unwarranted, as I had always known that 1/1/1753 was the low end on DATETIME. However, from a practical perspective, just what on earth value is there in using such values for a DATE data type? Certainly not the accurate measurement of the historical timeline... And I am an absolute stickler for using the right data type for the right reason. So maybe my saying SQL Server won't work with those "date values", I'm at least "historically accurate" ? Anyway, thanks for keeping me up to date....
Thanks for the feedback, Steve. You'll get no argument from me on the subject of accuracy when it comes to dates prior to 1753. The Gregorian calendar came to be the standard across many years with seemingly one country or area at a time slowly adopting it. There's also the subject of the inherent date errors that built up over time with the Julian and other calendars. Heh... then there's the subject of why there is no year 0000. 😉
Year 0 ? Actually a somewhat fascinating topic. Probably because at the time, 0 was still a relatively new concept, historically... and likely didn't start any kind of numerical sequence until at least a good 1500 to 1800 years later when mathematicians started using it that way.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2018 at 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. 🙁
Please post DDL and follow ANSI/ISO standards when asking for help.
July 16, 2018 at 3:58 pm
jcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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 Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 7:34 am
Jeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 18, 2018 at 10:39 am
Jeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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.
He probably helped set the standards for the Gregorian calendar since he boasts about setting everything else up....
July 18, 2018 at 12:11 pm
sgmunson - Tuesday, July 17, 2018 7:34 AMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 12:12 pm
meichmann - Wednesday, July 18, 2018 10:39 AMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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.
He probably helped set the standards for the Gregorian calendar since he boasts about setting everything else up....
He's old enough... he just may have! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 2:17 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.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 18, 2018 at 3:24 pm
Jeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 7:56 pm
meichmann - Wednesday, July 18, 2018 10:39 AMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PMHe 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.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 18, 2018 at 8:10 pm
jcelko212 32090 - Wednesday, July 18, 2018 2:17 PMmeichmann - Wednesday, July 18, 2018 10:39 AMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PMHe 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 8:23 pm
Jeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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
July 19, 2018 at 8:03 am
Nexana - Wednesday, July 18, 2018 8:23 PMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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 DateSET @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?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2018 at 9:44 am
Jeff Moden - Thursday, July 19, 2018 8:03 AMNexana - Wednesday, July 18, 2018 8:23 PMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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 DateSET @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.
July 19, 2018 at 10:03 am
Nexana - Thursday, July 19, 2018 9:44 AMJeff Moden - Thursday, July 19, 2018 8:03 AMNexana - Wednesday, July 18, 2018 8:23 PMJeff Moden - Monday, July 16, 2018 3:58 PMjcelko212 32090 - Monday, July 16, 2018 2:29 PM1753 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 DateSET @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.
Why mess with the WHILE loop.
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;
WITH e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt(n))
, e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b)
, e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b)
, eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2 a CROSS JOIN e4 b)
INSERT INTO [#Date]
SELECT
DATEADD(DAY,n - 1,@CurrentDate)
FROM
(SELECT TOP(DATEDIFF(DAY,@CurrentDate,@EndDate) + 1) n FROM [eTally]) dt(n);
SELECT MAX(DATE) AS DATE FROM #Date;
DROP TABLE #Date;
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply