Retrieving Dates SQL Server

Question

I was wondering if there is a way to retrieve a list of all dates in SQL Server. I know that using the getdate() function will retrieve the current date and time. Is there any function that will return a list of all dates?

given that there's approximately 14 billion "dates", and and infinite number of times for each of those dates, you're going to be waiting a while for your query results. And if you want to include FUTURE dates, then it'll be a few minutes longer too. — Marc B, Jul 11 '14 at 20:45
http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function - Already asked and answered — TMNT2014, Jul 11 '14 at 20:48

Siyual · Accepted Answer · 2014-07-11T20:53:07.437

3

Not from a built-in function, no, but you can create your own function that does this.

You can generate a list of dates by the following:

Declare @FromDate   Date = '2014-04-21',
        @ToDate     Date = '2014-05-02'

;With Date (Date) As
(
    Select  @FromDate Union All
    Select  DateAdd(Day, 1, Date)
    From    Date
    Where   Date < @ToDate
)
Select  Date
From    Date
Option  (MaxRecursion 0)

Using this logic, you can create your own function to do the same:

Create Function udf_GenerateDateRange(@From Date, @To Date)
Returns @Date Table
(
    Date Date
)
As Begin

    ;With Date (Date) As
    (
        Select  @From Union All
        Select  DateAdd(Day, 1, Date)
        From    Date
        Where   Date < @To
    )
    Insert  @Date
    Select  Date
    From    Date
    Option  (MaxRecursion 0)

    Return
End

Using the function you can select everything in the date range via:

Select * From udf_GenerateDateRange( '2014-01-01', '2014-05-10' )

I don't know what you mean by "all dates," but you should be able to pass in a Start and End Date, and it will return everything in-between.

edited Jul 11 '14 at 20:53

answered Jul 11 '14 at 20:45

Siyual

16,415
8
44
58

i can't down vote this because i have no idea what the OP is asking – T McKeown Jul 11 '14 at 20:45
I'm reading his question as "How do I generate a list of dates?" – Siyual Jul 11 '14 at 20:47
Thank you very much for your answer. That is exactly what I was looking for - even if I had difficulty discribing what I wanted haha – user3109653 Jul 11 '14 at 20:49
ok, now i will up vote, @user3109653 people will help you clarify but you need to give feedback – T McKeown Jul 11 '14 at 20:51

score 3 · Answer 2 · answered Jul 11 '14 at 20:48

This function will give you a list of dates from Start to Finish with a specified frequency.

IF OBJECT_ID('ListDates') IS NOT NULL DROP FUNCTION ListDates
GO

CREATE FUNCTION [dbo].[ListDates]
/* 
    returns a list of intervals with the given frequency that start after @StartDate and 
    before @EndDate. 
*/
(
    @Frequency int, 
    @StartDate DateTime, 
    @EndDate DateTime)
/*
    @Frequency: 
        0 - day, 
        1 - week, 
        2 - month
        3 - 3 months
        4 - 6 months
        5 - year
*/
returns @List TABLE (StartRange Date, EndRange Date)
BEGIN
    with dates as (
      SELECT cast(@StartDate as Date) [date]
      UNION ALL
      SELECT 
        CASE @Frequency
            WHEN 0 THEN DATEADD(day,1,t.date)
            WHEN 1 THEN DATEADD(week,1,t.date)
            WHEN 2 THEN DATEADD(month,1,t.date)
            WHEN 3 THEN DATEADD(month,3,t.date)
            WHEN 4 THEN DATEADD(month,6,t.date)
            WHEN 5 THEN DATEADD(year,1,t.date)
        END
      FROM dates t
      WHERE t.[date] < @EndDate
    )
    insert into @List (StartRange, EndRange)
    select 
        [Date],
        CASE @Frequency
            WHEN 0 THEN DATEADD(day,1,[Date])
            WHEN 1 THEN DATEADD(week,1,[Date])
            WHEN 2 THEN DATEADD(month,1,[Date])
            WHEN 3 THEN DATEADD(month,3,[Date])
            WHEN 4 THEN DATEADD(month,6,[Date])
            WHEN 5 THEN DATEADD(year,1,[Date])
        END
    from dates
    WHERE [Date] < @EndDate
    OPTION (MAXRECURSION 10000)
    RETURN
END
GO

Retrieving Dates SQL Server

2 Answers2