views:

3418

answers:

9

FYI: My question is duplicate of this MySQL question, but intended for SQL Server

Is there a function to write a query that will return a list of days between two dates?

For example, lets say there is a function called ExplodeDates:

SELECT ExplodeDates('2010-01-01', '2010-01-13');

This would return a single column table with the values:

2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13

Many thanks

Edit: I'm thinking that a calendar/numbers table might be able to help me here...

Update I decided to have a look at the three code answers provided, and the results of the execution - as a % of the total batch - are:

Rob Farley's answer : 18%

StingyJack's answer : 41%

KM's answer : 41%

Lower is better

I have accepted Rob Farley's answer, as it was the fastest, even though numbers table solutions (used by both KM and StingyJack in their answers) are something of a favourite of mine. Rob Farley's was two-thirds faster.

A: 

I'm no SQLServer expert, so I don't know if there is existing functionality there. You should use that if it's there.

If there is none, I imagine you could write a stored procedure that starts with the first date, loops through the days and adds them to some temporary table/result set until it reaches the end date.

Martin Klinke
I'm not a big fan of loops, and will avoid them if I have a choice. I'm also not great on temp tables. I used to work with SQL Server, but a lot of my knowledge has been lost over the last few years, but numbers tables is the one thing that stayed with me over that time.
Dan Atkinson
+1  A: 

A few ideas:

If you need the list dates in order to loop through them, you could have a Start Date and Day Count parameters and do a while loop whilst creating the date and using it?

Use C# CLR Stored Procedures and write the code in C#

Do this outside the database in code

Mark Redman
CLR Stored proc is def the way to go if performance is critical.
StingyJack
@StingyJack, no way. a Numbers table would be much more efficient, see my answer for an example of how.
KM
Performance is not critical, as this would only be called once an hour at worst, and on average, once a day, and then it's cached. I don't want to use a CLR to do this though.
Dan Atkinson
@KM - at risk of starting a flame war here, you should know that SQL is not designed to handle procedural ops, and performs poorly with them. If you need to do something like that, its best handled by application code.
StingyJack
@StingyJack, how is my function procedural? other than the validation check, it is a simple query that adding an offset to a fixed date for a viariable number of rows. Its not much different that caluclations done on in item details, like calculating total price based on qty and unit price with or without a currency.
KM
A: 

I'm an oracle guy, but I believe MS SQL Server has support for the connect by clause:

select  sysdate + level
from    dual
connect by level <= 10 ;

The output is:

SYSDATE+LEVEL
05-SEP-09
06-SEP-09
07-SEP-09
08-SEP-09
09-SEP-09
10-SEP-09
11-SEP-09
12-SEP-09
13-SEP-09
14-SEP-09

Dual is just a 'dummy' table that comes with oracle (it contains 1 row and the word 'dummy' as the value of the single column).

Brian
SQL Server doesn't have a built in table "dual", you need to create your own, like I do in my example code. I think "sysdate" is GETDATE() in SQL Server, and "connect by" is not valid syntax.
KM
Thus you might also SELECT from nowhere in SQL Server. SELECT GETDATE() is a valid line of code in SQL Server, not in Oracle, even though you replace the GETDATE() function by its SYSDATE homologue.
Will Marcouiller
You're right Brian, in Oracle we would do it this way. There are much interesting features in Oracle and PL/SQL that are not contained in TSQL and SQL Server. this is Sybase's fault! ;-) SQL Server is primarly based on Sysbase TSQL language.
Will Marcouiller
_SELECT GETDATE()_ will not produce a set only a single row. using dual in Oracle, you get a set.
KM
A single row is still a set.
StingyJack
+1  A: 

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

here is the function:

CREATE FUNCTION dbo.ListDates
(
     @StartDate    char(10)  
    ,@EndDate      char(10)
)
RETURNS
@DateList table
(
    Date datetime
)
AS
BEGIN


IF ISDATE(@StartDate)!=1 OR ISDATE(@EndDate)!=1
BEGIN
    RETURN
END

INSERT INTO @DateList
        (Date)
    SELECT
        CONVERT(datetime,@StartDate)+n.Number-1
        FROM Numbers  n
        WHERE Number<=DATEDIFF(day,@StartDate,CONVERT(datetime,@EndDate)+1)


RETURN

END --Function

use this:

select * from dbo.ListDates('2010-01-01', '2010-01-13')

output:

Date
-----------------------
2010-01-01 00:00:00.000
2010-01-02 00:00:00.000
2010-01-03 00:00:00.000
2010-01-04 00:00:00.000
2010-01-05 00:00:00.000
2010-01-06 00:00:00.000
2010-01-07 00:00:00.000
2010-01-08 00:00:00.000
2010-01-09 00:00:00.000
2010-01-10 00:00:00.000
2010-01-11 00:00:00.000
2010-01-12 00:00:00.000
2010-01-13 00:00:00.000

(13 row(s) affected)
KM
-1 for using loops
StingyJack
Where there's a "WHILE" there's a loop!
StingyJack
@StingyJack, are you nuts, there is no loop in my function. I use a loop to set up the Numbers table so people can easily see what it does. I could easily use a CTE there (like from here: http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum), but it confuses some people. For a one time set-up of a table it is not an issue.
KM
He's refering to where you input values into your Numbers table.
Mr. Smith
Clever approach.I like it!
Stefan
@KM.. there is certainly a loop in your code. Do me a favor before you bash me... test how long it takes to create the table of numbers using yours and my method for 10000 numbers. You will see the one I borrowed from Moden will beat the looped op by several orders of magnitude. Now after that, test how long each function takes. I am willing to bet that the solution I provided will perform better than the one you got. Next time you want to DV someone, you better make sure that your reasons are well founded.
StingyJack
I love the idea of numbers tables! They're ridiculously versatile and can be used for other things as well.
Dan Atkinson
@StingyJack, I did try your function, but it didn't work! you have an error in your code, once I fixed that if you run _SELECT * FROM dbo.ListDates('1/1/1997','12/31/1997') you get no dates back, but on mine you do. For my function you get dates no mater what starting date you use (1/1/1492, or 12/31/2500), for up to 8000 dates. You load your number table fast, but have to make it a throw aray #temp table to use IDENTITY(1,1) trick. The way I see it, set up a good number table one time and use it for many things. my numbers table took 2 seconds to set up, but I have to use many other times.
KM
@StingyJack, FYI - when I run both your function and my function for ('1/1/1998','12/31/2020') in SSMS, they both return 8401 rows in 0 seconds
KM
I ran a comparison on the three answers I felt were good, and yes, the execution times for your answer and StingyJack's was identical. Rob Farley's answer, however was about two-thirds (69%) as fast. Whilst performance is certainly not a critical issue in my case (this query isn't going to be run very often, and will have a very small date range to return), it is definitely a sway. I do agree with KM's argument that creating a temp number table (something that is enormously helpful) is pointless, and should be kept around for other uses.
Dan Atkinson
using SET STATISTICS TIME ON all three functoions called with ('1/1/1998','12/31/2020') report back the same CPU time = 0 ms, elapsed time = 1 ms. When calling Rob's and mine with ('1/1/1900','1921-11-27'), StingyJacks can't do that date range I get Rob's as: CPU time = 93 ms, elapsed time = 93 ms. and I get mine: CPU time = 0 ms, elapsed time = 1 ms., mine looks way better. What testing method do you use @Dan Atkinson? if you included the one time Number table set-up, that is a VERY FLAWED way, as it does not reflect the actual in-use perfomrance
KM
My code doesn't have a pre-existing numbers table. If you already have the numbers table, then you should use SELECT DATEADD(day,num-1,@startdate) as thedateFROM numsWHERE num <= DATEDIFF(day,@startdate,@enddate) + 1... But do it in one statement, using "returns table as return (...)", so that the Query Optimizer cacn simplify it out.
Rob Farley
Also - why on earth would you make a function to handle dates that uses char(10) parameters? Why not make it date/datetime?
Rob Farley
@Rob Farley, you don't need to use DATEADD(day,...) you can just "+" and "-" to a datetime to work on day increments. Actually your number table query has the same execution plan as mine. Also I use the char(10) to remove time (which your function does not do, was it necessary? OP never says, I did it anyway) and allow the function to validate the date without failing when called. OP didn't say if validation was necessary, so I threw it in just in case, you can easily remove it if needed and then return the query as you suggest.
KM
A: 

Would all these dates be in the database already or do you just want to know the days between the two dates? If it's the first you could use the BETWEEN or <= >= to find the dates between

EXAMPLE:

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

OR

SELECT column_name(s)
FROM table_name
WHERE column_name
value1 >= column_name
AND column_name =< value2
Phill Pafford
+2  A: 

Try something like this:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

You then use:

SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;

Edited (after the acceptance):

Please note... if you already have a sufficiently large nums table then you should use:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

And you can create such a table using:

CREATE TABLE dbo.nums (num int PRIMARY KEY);
INSERT dbo.nums values (1);
GO
INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums
GO 20

These lines will create a table of numbers containing 1M rows... and far quicker than inserting them one by one.

You should NOT create your ExplodeDates function using a function that involves BEGIN and END, as the Query Optimizer becomes unable to simplify the query at all.

Rob Farley
Thank you for taking the time to improve your answer. I also wasn't aware that using BEGIN and END prevents Query Optimizer from doing its job. Thanks!
Dan Atkinson
A: 

Definately a numbers table, though tyou may want to use Mark Redman's idea of a CLR proc/assembly if you really need the performance.

How to create the table of dates (and a super fast way to create a numbers table)

/*Gets a list of integers into a temp table (Jeff Moden's idea from SqlServerCentral.com)*/
 SELECT TOP 10950 /*30 years of days*/
        IDENTITY(INT,1,1) as N
   INTO #Numbers
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2


/*Create the dates table*/
CREATE TABLE [TableOfDates](
    [fld_date] [datetime] NOT NULL,
 CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED 
(
    [fld_date] ASC
)WITH FILLFACTOR = 99 ON [PRIMARY]
) ON [PRIMARY]

/*fill the table with dates*/
DECLARE @daysFromFirstDateInTheTable int
DECLARE @firstDateInTheTable DATETIME

SET @firstDateInTheTable = '01/01/1998'
SET @daysFromFirstDateInTheTable = (SELECT (DATEDIFF(dd, @firstDateInTheTable ,GETDATE()) + 1))

INSERT INTO
      TableOfDates
SELECT 
      DATEADD(dd,nums.n - @daysFromFirstDateInTheTable, CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date
FROM #Numbers nums

Now that you have a table of dates, you can use a function (NOT A PROC) like KM's to get the table of them.

CREATE FUNCTION dbo.ListDates
(
     @StartDate    DATETIME  
    ,@EndDate      DATETIME
)
RETURNS
@DateList table
(
    Date datetime
)
AS
BEGIN

/*add some validation logic of your own to make sure that the inputs are sound.Adjust the rest as needed*/

  INSERT INTO
    @DateList
  SELECT FLD_Date FROM TableOfDates (NOLOCK) WHERE FLD_Date >= @StartDate AND FLD_Date <= @EndDate
  RETURN
END
StingyJack
why do you need a table of dates, you just calculate them using your Numbers table??
KM
because calculating them on the fly can cause poor performance, especially if they are used inline and evaluated for each row accessed by the statement.
StingyJack
**Msg 137, Level 15, State 2, Line 23 Must declare the scalar variable "@".** this _(SELECT (DATEDIFF(dd, @ firstDateInTheTable ,GETDATE()) + 1))_ should be (SELECT (DATEDIFF(dd, @firstDateInTheTable ,GETDATE()) + 1))_
KM
WOW...you found a space between the @ and the variable name. I'm impressed.
StingyJack
A: 

Perhaps if you wish to go an easier way, this should do it.

WITH date_range (calc_date) AS (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 6, 0)
        UNION ALL SELECT DATEADD(DAY, 1, calc_date)
            FROM date_range
            WHERE DATEADD(DAY, 1, calc_date) < CURRENT_TIMESTAMP)
SELECT calc_date
FROM date_range;

But the temporary table is a very good approach also. Perhaps shall you also consider a populated calendar table.

Will Marcouiller
You only have to create a stored procedure with this code, and perhaps replace the CURRENT_TIMESTAMP values with yours or something like this.
Will Marcouiller
A: 

All you have to do is just change the hard coded value in the code provided below

DECLARE @firstDate datetime DECLARE @secondDate datetime DECLARE @totalDays INT SELECT @firstDate = getDate() - 30 SELECT @secondDate = getDate()

DECLARE @index INT
SELECT @index = 0
SELECT @totalDays = datediff(day, @firstDate, @secondDate)

CREATE TABLE #temp
(
     ID INT NOT NULL IDENTITY(1,1)
    ,CommonDate DATETIME NULL
)

WHILE @index < @totalDays
    BEGIN

     INSERT INTO #temp (CommonDate) VALUES  (DATEADD(Day, @index, @firstDate)) 
     SELECT @index = @index + 1
    END

SELECT CONVERT(VARCHAR(10), CommonDate, 102) as [Date Between] FROM #temp

DROP TABLE #temp
Shiva