January 23, 2018 at 10:59 am
jcelko212 32090 - Tuesday, January 23, 2018 8:30 AM
BWAAAA-HAAAAA-HAAAAA!!!! Listen to you, mister "I really like the MySQL ability to store months as YYYY-MM-00 and years as YYYY-00-00", which is also non-ANSI/ISO compliant and a proprietary kludge, BTW.
As I've explained many times, the reasons I like this notation is that it follows the existing ISO 8601 makes a simple extension to the standard. Is being debated, but I honestly don't know what it's current status is within ANSI and ISO. It sorts with the existing ANSI/ISO standard display format, and works well with the temporal interval data type standards.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 23, 2018 at 11:43 am
Jeff Moden - Tuesday, January 23, 2018 9:47 AMjcelko212 32090 - Tuesday, January 23, 2018 8:30 AMBWAAAA-HAAAAA-HAAAAA!!!! Listen to you, mister "I really like the MySQL ability to store months as YYYY-MM-00 and years as YYYY-00-00", which is also non-ANSI/ISO compliant and a proprietary kludge, BTW.
As I've explained many times, the reasons I like this notation is that it follows the existing ISO 8601 makes a simple extension to the standard. Is being debated, but I honestly don't know what it's current status is within ANSI and ISO. It sorts with the existing ANSI/ISO standard display format, and works well with the temporal interval data type standards.
Could they consider a different SQL data type specifically designed to allow a day of '0'? Otherwise normal date validation could be corrupted by erroneously allowing a day of 0. Or is day 0 to be used only for queries, and not allowed to be entered?? Don't really see how that would work truly cleanly.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 23, 2018 at 11:49 am
Jeff Moden - Tuesday, January 23, 2018 9:47 AMjcelko212 32090 - Tuesday, January 23, 2018 8:30 AMBWAAAA-HAAAAA-HAAAAA!!!! Listen to you, mister "I really like the MySQL ability to store months as YYYY-MM-00 and years as YYYY-00-00", which is also non-ANSI/ISO compliant and a proprietary kludge, BTW.
As I've explained many times, the reasons I like this notation is that it follows the existing ISO 8601 makes a simple extension to the standard. Is being debated, but I honestly don't know what it's current status is within ANSI and ISO. It sorts with the existing ANSI/ISO standard display format, and works well with the temporal interval data type standards.
You need to go back and read the standards then. No where do they allow for "00" of any date part and no such extension in the standards currently exist. Further, it violates several of your other hotly debated principles... it stores a date as a character based object, it's proprietary, which makes it non-portable, and because it's in that format, it also qualifies as a presentation layer format, which you just got done bitching about.
At any rate, you need to stop insulting people with your passive-aggressive hoo-haa because your slate is far from clean.
--Jeff Moden
January 23, 2018 at 1:29 pm
Just for S and G's I thought I'd throw in a solution. sorry for not commenting it.
DECLARE @NumberOfMonths INT
, @CurrentDate DATE;
SELECT @NumberOfMonths = 24, @CurrentDate = GETDATE();
WITH eTally AS (
SELECT TOP (@NumberOfMonths)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS [n]
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt3(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt4(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt5(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt6(n)
), BaseDates AS (
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n],0) AS [StartDate]
, DATEADD(DAY, -1, DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n] + 1,0)) AS [EndDate]
FROM
[eTally] AS [et]
)
SELECT
YEAR([bd].[StartDate]) CalendarYear
, LEFT(DATENAME(MONTH,[bd].[StartDate]),3) NameOfMonth
, [bd].[StartDate]
, [bd].[EndDate]
FROM
BaseDates AS [bd];
This could easily be made an itvf if needed.
January 23, 2018 at 1:30 pm
Jeff Moden - Tuesday, January 23, 2018 9:47 AMjcelko212 32090 - Tuesday, January 23, 2018 8:30 AMBWAAAA-HAAAAA-HAAAAA!!!! Listen to you, mister "I really like the MySQL ability to store months as YYYY-MM-00 and years as YYYY-00-00", which is also non-ANSI/ISO compliant and a proprietary kludge, BTW.
As I've explained many times, the reasons I like this notation is that it follows the existing ISO 8601 makes a simple extension to the standard. Is being debated, but I honestly don't know what it's current status is within ANSI and ISO. It sorts with the existing ANSI/ISO standard display format, and works well with the temporal interval data type standards.
And as you have always said, don't use dialect specific extensions. Hypocrite much?
January 23, 2018 at 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?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 23, 2018 at 2:33 pm
ScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?
IMHO, it is an atomic month AND it represents a range of dates in the same way that a single date is atomic and represents a range of time from 00:00 to 23:59:59.99x.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2018 at 2:57 pm
drew.allen - Tuesday, January 23, 2018 2:33 PMScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?IMHO, it is an atomic month AND it represents a range of dates in the same way that a single date is atomic and represents a range of time from 00:00 to 23:59:59.99x.
Drew
Every date in a table represents a single date ... until day 0 comes along. Then some rows are a single day, others are not. I don't see how an "atomic day" and an "atomic month" in the same column is consistent.
So when I join the "atomic" date of 01-00 to another table with dates of 01-00 and other 01-nn dates, what is the result? What if the other table contains only 01-nn dates?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 23, 2018 at 3:13 pm
ScottPletcher - Tuesday, January 23, 2018 2:57 PMdrew.allen - Tuesday, January 23, 2018 2:33 PMScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?IMHO, it is an atomic month AND it represents a range of dates in the same way that a single date is atomic and represents a range of time from 00:00 to 23:59:59.99x.
Drew
Every date in a table represents a single date ... until day 0 comes along. Then some rows are a single day, others are not. I don't see how an "atomic day" and an "atomic month" in the same column is consistent.
So when I join the "atomic" date of 01-00 to another table with dates of 01-00 and other 01-nn dates, what is the result? What if the other table contains only 01-nn dates?
In order for it to make sense, they have to represent datetime intervals. The interval data type is supported in the standards, but not by SQL Server, which is probably part of the reason that SQL Server hasn't implemented this convention.
If you think of them as intervals, it doesn't matter that one represents a smaller interval than the other.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2018 at 3:33 pm
Lynn Pettis - Tuesday, January 23, 2018 1:29 PMJust for S and G's I thought I'd throw in a solution. sorry for not commenting it.
DECLARE @NumberOfMonths INT
, @CurrentDate DATE;SELECT @NumberOfMonths = 24, @CurrentDate = GETDATE();
WITH eTally AS (
SELECT TOP (@NumberOfMonths)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS [n]
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt3(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt4(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt5(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt6(n)
), BaseDates AS (
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n],0) AS [StartDate]
, DATEADD(DAY, -1, DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n] + 1,0)) AS [EndDate]
FROM
[eTally] AS [et]
)
SELECT
YEAR([bd].[StartDate]) CalendarYear
, LEFT(DATENAME(MONTH,[bd].[StartDate]),3) NameOfMonth
, [bd].[StartDate]
, [bd].[EndDate]
FROM
BaseDates AS [bd];This could easily be made an itvf if needed.
Thanks Lynn for the code, more than what Jeff did. 😀 That's a joke Jeff, you given me plenty to digest. "handy man's secret weapon".
Just playing with your code Lynn, and I don't think anybody would want to go back this far. But why does the code error if you try more than 3181 months?Msg 517, Level 16, State 1, Line 6
Adding a value to a 'datetime' column caused an overflow.
Last date shown is '01/01/1753'.
Just curious why your code gets that error, my loop doesn't give me that error until 24205 months, back to 01/01/0001.
I know no one would need or want to go back that far, just playing with the code. My assumption is it is with the DATEDIFF.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 23, 2018 at 3:44 pm
drew.allen - Tuesday, January 23, 2018 3:13 PMScottPletcher - Tuesday, January 23, 2018 2:57 PMdrew.allen - Tuesday, January 23, 2018 2:33 PMScottPletcher - Tuesday, January 23, 2018 2:24 PMDoes YYYY-MM-00 raise 1NF / atomicity issues? Does the column represent an atomic date or a range of dates? Is the range somehow deemed atomic?IMHO, it is an atomic month AND it represents a range of dates in the same way that a single date is atomic and represents a range of time from 00:00 to 23:59:59.99x.
Drew
Every date in a table represents a single date ... until day 0 comes along. Then some rows are a single day, others are not. I don't see how an "atomic day" and an "atomic month" in the same column is consistent.
So when I join the "atomic" date of 01-00 to another table with dates of 01-00 and other 01-nn dates, what is the result? What if the other table contains only 01-nn dates?
In order for it to make sense, they have to represent datetime intervals. The interval data type is supported in the standards, but not by SQL Server, which is probably part of the reason that SQL Server hasn't implemented this convention.
If you think of them as intervals, it doesn't matter that one represents a smaller interval than the other.
Drew
It does matter if you try to "overload" a date to include intervals. No such problems with a separate "interval" data type, as some dbms's do. But they don't store it as "yyyy-mm-00" or "yyyy-00-00", which doesn't fit any consistent data usage alongside "yyyy-mm-dd".
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 23, 2018 at 4:01 pm
below86 - Tuesday, January 23, 2018 3:33 PMLynn Pettis - Tuesday, January 23, 2018 1:29 PMJust for S and G's I thought I'd throw in a solution. sorry for not commenting it.
DECLARE @NumberOfMonths INT
, @CurrentDate DATE;SELECT @NumberOfMonths = 24, @CurrentDate = GETDATE();
WITH eTally AS (
SELECT TOP (@NumberOfMonths)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS [n]
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt3(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt4(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt5(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt6(n)
), BaseDates AS (
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n],0) AS [StartDate]
, DATEADD(DAY, -1, DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n] + 1,0)) AS [EndDate]
FROM
[eTally] AS [et]
)
SELECT
YEAR([bd].[StartDate]) CalendarYear
, LEFT(DATENAME(MONTH,[bd].[StartDate]),3) NameOfMonth
, [bd].[StartDate]
, [bd].[EndDate]
FROM
BaseDates AS [bd];This could easily be made an itvf if needed.
Thanks Lynn for the code, more than what Jeff did. 😀 That's a joke Jeff, you given me plenty to digest. "handy man's secret weapon".
Just playing with your code Lynn, and I don't think anybody would want to go back this far. But why does the code error if you try more than 3181 months?Msg 517, Level 16, State 1, Line 6
Adding a value to a 'datetime' column caused an overflow.
Last date shown is '01/01/1753'.
Just curious why your code gets that error, my loop doesn't give me that error until 24205 months, back to 01/01/0001.
I know no one would need or want to go back that far, just playing with the code. My assumption is it is with the DATEDIFF.
Using 0 (zero) as the date in computation results in an implicit conversion to the datetime data type which is why you get the error. To resolve that use the following code:
DECLARE @NumberOfMonths INT
, @CurrentDate DATE;
SELECT @NumberOfMonths = 4000, @CurrentDate = GETDATE();
WITH eTally AS (
SELECT TOP (@NumberOfMonths)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS [n]
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt3(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt4(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt5(n)
CROSS JOIN
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt6(n)
), BaseDates AS (
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n],CAST('1900-01-01' AS DATE)) AS [StartDate]
, DATEADD(DAY, -1, DATEADD(MONTH,DATEDIFF(MONTH,0,@CurrentDate) - [et].[n] + 1,CAST('1900-01-01' AS DATE))) AS [EndDate]
FROM
[eTally] AS [et]
)
SELECT
YEAR([bd].[StartDate]) CalendarYear
, LEFT(DATENAME(MONTH,[bd].[StartDate]),3) NameOfMonth
, [bd].[StartDate]
, [bd].[EndDate]
FROM
BaseDates AS [bd];
January 23, 2018 at 4:04 pm
Other than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 23, 2018 at 4:10 pm
ScottPletcher - Tuesday, January 23, 2018 4:04 PMOther than the use of cte, I don't see any fundamental difference between your code and what I posted earlier, although I admit I haven't thoroughly reviewed it, since I'm exceptionally, extraordinarily busy at work right now.
There isn't much different, except I refuse to use a semicolon (;) as a begininator when writing code that uses a CTE. I ensure the previous statement is properly terminated with a semicolon. The semicolon is a terminator.
January 23, 2018 at 4:11 pm
Oh, when you go back past 1753 the dates may not actually be correct. I leave that to you to research.
Viewing 15 posts - 46 through 60 (of 104 total)
You must be logged in to reply to this topic. Login to reply