September 7, 2018 at 4:23 pm
Thom A - Friday, September 7, 2018 9:27 AMAhhh!!! You ruined my "beautiful" tally table and introduced an RBAR CTE. Why would you do that... :crying:You have a
TOP 10 in there as well, but no ORDER BY, so you want just 10 random last names? What are you actually trying to achieve here? I think it's time for some sample data, expected results and a re-explanation of your goals.
What Thom posted will work much better than a recursive CTE. I used to use them all the time but have changed all my code to use a tally tables. It's much, much, much more efficient. Read Jeff Moden's article here and this is a good article too explaining the evolution of tally tables in T-SQL.
September 7, 2018 at 5:11 pm
drew.allen - Friday, September 7, 2018 3:07 PMYou want to be careful here. The DATEDIFF function causes an overflow quite quickly when using milliseconds.
Drew
In the code I pasted in earlier runs out of the number of values returned before it will overflow the DATEDIFF function.
The DATEDIFF function will return values of up to 2,147,483,647 (231 − 1) which is the maximum of a two's-complement 4 byte integer.
You have to have a lot of milliseconds for it to overflow!
If you want my function to produce more than 2,147,483,647 rows you'd have to use DATEDIFF_BIG, but you'd have to be on SQL Server 2016 or higher, I thought the number of rows my function produces would be enough to suite almost any needs.
I've amended the function so it will return the maximum number of rows that DATEDIFF can produce. I've also added ns and mcsIF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X')
END
GO
/*-- **********************************************************************
-- FUNCTION: DateRange
-- Returns a table of datetime values based on the parameters
-- Parameters:
-- @Start :Start date of the series
-- @End :End date of the series
-- @Unit :The time unit for @interval
-- ns : nanoseconds
-- mcs : microseconds
-- ms : milliseconds
-- ss : seconds
-- mi : minutes
-- hh : hours
-- dd : days
-- ww : weeks
-- mm : months
-- qq : quarters
-- yy : years
-- @Interval :The number of units between each interval
-- Sample Call
-- SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss',2)
-- SELECT COUNT(*) FROM [dbo].[DateRange]('2011-01-01 10:24:35', '2011-01-25 23:24:35', 'ms',1)
-- SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', 'dd', 9)
-- **********************************************************************/
ALTER FUNCTION [dbo].[DateRange] (@Start datetime2, @End datetime2, @Unit nvarchar(3), @interval int)
RETURNS TABLE
AS
RETURN
WITH A(A) AS (SELECT 'Anything' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
B(RowNum) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1 A))-1 FROM A A, A B, A C, A D, A E, A F, A G, A H)
SELECT TOP(CASE @Unit
WHEN 'ns' THEN DATEDIFF(ns,@Start,@End)/@Interval
WHEN 'mcs' THEN DATEDIFF(mcs,@Start,@End)/@Interval
WHEN 'ms' THEN DATEDIFF(ms,@Start,@End)/@Interval
WHEN 'ss' THEN DATEDIFF(ss,@Start,@End)/@Interval
WHEN 'mi' THEN DATEDIFF(mi,@Start,@End)/@Interval
WHEN 'hh' THEN DATEDIFF(hh,@Start,@End)/@Interval
WHEN 'dd' THEN DATEDIFF(dd,@Start,@End)/@Interval
WHEN 'ww' THEN DATEDIFF(ww,@Start,@End)/@Interval
WHEN 'mm' THEN DATEDIFF(mm,@Start,@End)/@Interval
WHEN 'qq' THEN DATEDIFF(qq,@Start,@End)/@Interval
WHEN 'yy' THEN DATEDIFF(yy,@Start,@End)/@Interval
ELSE DATEDIFF(dd,@Start,@End)/@Interval
END+1)
CASE @Unit
WHEN 'ns' THEN DATEADD(ns, @interval * RowNum, @Start)
WHEN 'mcs' THEN DATEADD(mcs, @interval * RowNum, @Start)
WHEN 'ms' THEN DATEADD(ms, @interval * RowNum, @Start)
WHEN 'ss' THEN DATEADD(ss, @interval * RowNum, @Start)
WHEN 'mi' THEN DATEADD(mi, @interval * RowNum, @Start)
WHEN 'hh' THEN DATEADD(hh, @interval * RowNum, @Start)
WHEN 'dd' THEN DATEADD(dd, @interval * RowNum, @Start)
WHEN 'ww' THEN DATEADD(ww, @interval * RowNum, @Start)
WHEN 'mm' THEN DATEADD(mm, @interval * RowNum, @Start)
WHEN 'qq' THEN DATEADD(qq, @interval * RowNum, @Start)
WHEN 'yy' THEN DATEADD(yy, @interval * RowNum, @Start)
ELSE DATEADD(dd, @interval * RowNum, @Start)
END AS Value
FROM B
GO
September 8, 2018 at 3:49 pm
DATEDIFF_BIG is the bad joke that MS came up with because they royally screwed up when they created the relatively new DATETIME2, DATE, and TIME datatypes, which can no longer do direct date math such as Period = Enddatetime - Startdatetime, which the DATETIME datatype does quite nicely and IS both an ANSI and ISO standard for how dates, times, and periods are supposed to be handled.
Also, 2,147,483,647 milliseconds isn't actually a very long time at all. It's only 25 days + 20:31:23.647 (hh:mi:ss.mil).
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2018 at 7:04 pm
Jeff Moden - Saturday, September 8, 2018 3:49 PMDATEDIFF_BIG is the bad joke that MS came up with because they royally screwed up when they created the relatively new DATETIME2, DATE, and TIME datatypes, which can no longer do direct date math such as Period = Enddatetime - Startdatetime, which the DATETIME datatype does quite nicely and IS both an ANSI and ISO standard for how dates, times, and periods are supposed to be handled.Also, 2,147,483,647 milliseconds isn't actually a very long time at all. It's only 25 days + 20:31:23.647 (hh:mi:ss.mil).
It's not long in days but it is long in milliseconds. I did also provide the facility in that function to get all the years between two dates which won't even take you back to when the earth was created..
September 9, 2018 at 9:10 am
Jonathan AC Roberts - Saturday, September 8, 2018 7:04 PMJeff Moden - Saturday, September 8, 2018 3:49 PMDATEDIFF_BIG is the bad joke that MS came up with because they royally screwed up when they created the relatively new DATETIME2, DATE, and TIME datatypes, which can no longer do direct date math such as Period = Enddatetime - Startdatetime, which the DATETIME datatype does quite nicely and IS both an ANSI and ISO standard for how dates, times, and periods are supposed to be handled.Also, 2,147,483,647 milliseconds isn't actually a very long time at all. It's only 25 days + 20:31:23.647 (hh:mi:ss.mil).
It's not long in days but it is long in milliseconds. I did also provide the facility in that function to get all the years between two dates which won't even take you back to when the earth was created..
Just to be sure, I wasn't slamming your function at all. It's well written, nicely formatted, and properly documented. You should write a "Spackle" article about it here on SSC.
Your comment about milli-seconds and, more specifically, DATEDIFF_BIG simply brought out the extreme exasperation that I have with MS in that they've crippled the newer datatypes compared to the functionality that the DATETIME (and SMALLDATETIME, if you ever use it) have always had and I use such functionality (particularly the Period = EndDateTime - StartDateTime functionality and the aggregation of such periods over the depth of some rather large tables) a whole lot. Even spreadsheets have that down pat.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2018 at 12:32 pm
Jeff Moden - Sunday, September 9, 2018 9:10 AMJust to be sure, I wasn't slamming your function at all. It's well written, nicely formatted, and properly documented. You should write a "Spackle" article about it here on SSC.
Your comment about milli-seconds and, more specifically, DATEDIFF_BIG simply brought out the extreme exasperation that I have with MS in that they've crippled the newer datatypes compared to the functionality that the DATETIME (and SMALLDATETIME, if you ever use it) have always had and I use such functionality (particularly the Period = EndDateTime - StartDateTime functionality and the aggregation of such periods over the depth of some rather large tables) a whole lot. Even spreadsheets have that down pat.
Thanks Jeff, I thought it would be quite a useful function for people who want to left join on all dates between a range in case the table they joining to has missing dates. I'm not sure it's worthy of a spackle (or polyfilla as we call it in the UK) but I'll add it to the scripts.
I've never actually used DATEDIFF_BIG so have very little knowledge of it, I just quickly looked it up on the internet.
September 10, 2018 at 6:08 am
Jonathan AC Roberts - Sunday, September 9, 2018 12:32 PMThanks Jeff, I thought it would be quite a useful function for people who want to left join on all dates between a range in case the table they joining to has missing dates. I'm not sure it's worthy of a spackle (or polyfilla as we call it in the UK) but I'll add it to the scripts.
I've never actually used DATEDIFF_BIG so have very little knowledge of it, I just quickly looked it up on the internet.
DATEDIFF_BIG works fine. My only problem with it is that it's a patch rather than a fix for how the crippled the newer datatypes.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:18 am
Jonathan AC Roberts - Sunday, September 9, 2018 12:32 PMThanks Jeff, I thought it would be quite a useful function for people who want to left join on all dates between a range in case the table they joining to has missing dates. I'm not sure it's worthy of a spackle (or polyfilla as we call it in the UK) but I'll add it to the scripts.
I've never actually used DATEDIFF_BIG so have very little knowledge of it, I just quickly looked it up on the internet.
These are always useful articles to help someone learn for this particular problem.
September 10, 2018 at 7:26 am
Steve Jones - SSC Editor - Monday, September 10, 2018 7:18 AMJonathan AC Roberts - Sunday, September 9, 2018 12:32 PMThanks Jeff, I thought it would be quite a useful function for people who want to left join on all dates between a range in case the table they joining to has missing dates. I'm not sure it's worthy of a spackle (or polyfilla as we call it in the UK) but I'll add it to the scripts.
I've never actually used DATEDIFF_BIG so have very little knowledge of it, I just quickly looked it up on the internet.These are always useful articles to help someone learn for this particular problem.
Agreed. And while it may seem to be too short a subject for a "Spackle" article, if you take the time to explain what's happening in the code, then 2 things happen... people not only have a useful script but they learn something in the process. Your function is an excellent candidate for that type of thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:51 am
Jonathan AC Roberts - Saturday, September 8, 2018 7:04 PMIt's not long in days but it is long in milliseconds. I did also provide the facility in that function to get all the years between two dates which won't even take you back to when the earth was created..
You and I both know that, but other people who see this might not. Unless you've actually tested, most people won't suspect that it only takes 25+ days to overflow datediff when using milliseconds.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 10, 2018 at 8:05 am
drew.allen - Monday, September 10, 2018 7:51 AMYou and I both know that, but other people who see this might not. Unless you've actually tested, most people won't suspect that it only takes 25+ days to overflow datediff when using milliseconds.Drew
Well they would get exactly the same problem and error if they used SQL Server's DATEDIFF function directly with ms on values that return more than 2 billion. It gets worse if you use ns (nanoseconds), you would get just over 2 seconds back before it overflowed.
I'm not sure what your point is?
September 10, 2018 at 8:13 am
Jonathan AC Roberts - Monday, September 10, 2018 8:05 AMWell they would get exactly the same problem and error if they used SQL Server's DATEDIFF function directly with ms on values that return more than 2 billion. It gets worse if you use ns (nanoseconds), you would get just over 2 seconds back before it overflowed.
I'm not sure what your point is?
I think part of what everyone's point is, is why did they introduce DATEDIFF_BIG instead of changing the return datatype of DATEDIFF on more recent version of SQL Server? They could have simply listed the change in the breaking changes and then people would be aware to cater for it on more recent versions if it was an issue. I'm sure theyv'e changed the return datatype of other functions before (none spring to mind right now though), so why not here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 10, 2018 at 8:15 am
Thom A - Monday, September 10, 2018 8:13 AMJonathan AC Roberts - Monday, September 10, 2018 8:05 AMWell they would get exactly the same problem and error if they used SQL Server's DATEDIFF function directly with ms on values that return more than 2 billion. It gets worse if you use ns (nanoseconds), you would get just over 2 seconds back before it overflowed.
I'm not sure what your point is?I think part of what everyone's point is, is why did they introduce DATEDIFF_BIG instead of changing the return datatype of DATEDIFF on more recent version of SQL Server? They could have simply listed the change in the breaking changes and then people would be aware to cater for it on more recent versions if it was an issue. I'm sure theyv'e changed the return datatype of other functions before (none spring to mind right now though), so why not here.
Thanks for the feedback Thom. When I write the article I'm going to add a line that "2,147,483,647 ms is 25 days + 20:31:23.647 (hh:mi:ss.mil)"
Maybe I need two functions, DateRange and DateRange_Big?
September 10, 2018 at 8:52 am
Jonathan AC Roberts - Monday, September 10, 2018 8:05 AMdrew.allen - Monday, September 10, 2018 7:51 AMJonathan AC Roberts - Saturday, September 8, 2018 7:04 PMJeff Moden - Saturday, September 8, 2018 3:49 PMDATEDIFF_BIG is the bad joke that MS came up with because they royally screwed up when they created the relatively new DATETIME2, DATE, and TIME datatypes, which can no longer do direct date math such as Period = Enddatetime - Startdatetime, which the DATETIME datatype does quite nicely and IS both an ANSI and ISO standard for how dates, times, and periods are supposed to be handled.Also, 2,147,483,647 milliseconds isn't actually a very long time at all. It's only 25 days + 20:31:23.647 (hh:mi:ss.mil).
It's not long in days but it is long in milliseconds. I did also provide the facility in that function to get all the years between two dates which won't even take you back to when the earth was created..
You and I both know that, but other people who see this might not. Unless you've actually tested, most people won't suspect that it only takes 25+ days to overflow datediff when using milliseconds.
Drew
Well they would get exactly the same problem and error if they used SQL Server's DATEDIFF function directly with ms on values that return more than 2 billion. It gets worse if you use ns (nanoseconds), you would get just over 2 seconds back before it overflowed.
I'm not sure what your point is?
My point is that you need to keep defensive programming in mind. You're trying to make your code as widely applicable as possible, and I'm not sure that's a good idea. I've never seen a situation where I need to measure the same attribute over that wide a range of scales, and that you might be better off limiting your scales to ones that make sense for the attribute that you are measuring.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 10, 2018 at 8:56 am
Jonathan AC Roberts - Monday, September 10, 2018 8:15 AMThom A - Monday, September 10, 2018 8:13 AMJonathan AC Roberts - Monday, September 10, 2018 8:05 AMWell they would get exactly the same problem and error if they used SQL Server's DATEDIFF function directly with ms on values that return more than 2 billion. It gets worse if you use ns (nanoseconds), you would get just over 2 seconds back before it overflowed.
I'm not sure what your point is?I think part of what everyone's point is, is why did they introduce DATEDIFF_BIG instead of changing the return datatype of DATEDIFF on more recent version of SQL Server? They could have simply listed the change in the breaking changes and then people would be aware to cater for it on more recent versions if it was an issue. I'm sure theyv'e changed the return datatype of other functions before (none spring to mind right now though), so why not here.
Thanks for the feedback Thom. When I write the article I'm going to add a line that "2,147,483,647 ms is 25 days + 20:31:23.647 (hh:mi:ss.mil)"
Maybe I need two functions, DateRange and DateRange_Big?
Heh... now... about those nano-seconds. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 59 total)
You must be logged in to reply to this topic. Login to reply