0

I am trying to write a query that will run all the days and the name of the day between two set dates.

Example:

Date1 = 12/28/2005
Date2 = 12/30/2006

Results:

12/28/2005 Wednesday
12/29/2005 Thursday
12/30/2005 Friday
12/31/2005 Saturday
01/01/2006 Sunday
01/02/2006 Monday
01/03/2006 Tuesday

Any help is appreciated!

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Nitin
  • 65
  • 8

3 Answers3

0

You may check this fiddle.

The code:

DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME

SET @Date1 = '20051228'
SET @Date2 = '20061230'

;WITH cteSequence ( SeqNo) as
(
      SELECT 0
      UNION ALL
      SELECT SeqNo + 1
      FROM cteSequence
      WHERE SeqNo < DATEDIFF(d,@Date1,@Date2)
)
SELECT CONVERT(VARCHAR,DATEADD(d,SeqNo,@Date1),1) + ' ' + DATENAME(dw,DATEADD(d,SeqNo,@Date1))
FROM cteSequence
OPTION ( MAXRECURSION 0);


GO
Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
0

You can use that table-valued function:

create function DateTable
(
  @FirstDate datetime,
  @LastDate datetime,
  @handle nvarchar(10)='day',
  @handleQuantity int=1
)
returns @datetable table (
  [date] datetime
)
AS
begin

  with CTE_DatesTable
  as 
  (
    select @FirstDate AS [date]
    union ALL
    select case @handle
        when 'month' then dateadd(month, @handleQuantity, [date])
        when 'year' then dateadd(year, @handleQuantity, [date])
        when 'hour' then dateadd(hour, @handleQuantity, [date])
        when 'minute' then dateadd(minute, @handleQuantity, [date])
        when 'second' then dateadd(second, @handleQuantity, [date])
        else dateadd(day, @handleQuantity, [date])
        end
    from CTE_DatesTable
    where @LastDate >=
    case @handle
        when 'month' then dateadd(month, @handleQuantity, [date])
        when 'year' then dateadd(year, @handleQuantity, [date])
        when 'hour' then dateadd(hour, @handleQuantity, [date])
        when 'minute' then dateadd(minute, @handleQuantity, [date])
        when 'second' then dateadd(second, @handleQuantity, [date])
        else DATEADD(day, @handleQuantity, [date])
        end
  )
  insert @datetable ([date])
  select [date] from CTE_DatesTable
  option (MAXRECURSION 0)

  return
end

You can call it like:

select [date],datepart(weekday,[date]) from dbo.DateTable('12/28/2005','12/30/2006',default,default)
inon
  • 1,689
  • 1
  • 19
  • 34
0

You didn't specify your DBMS so I'm assuming Postgres:

select i::date, to_char(i, 'Day')
from generate_series(timestamp '2005-12-28 00:00:00', 
                     timestamp '2006-12-30 00:00:00', interval '1' day) i;

The ANSI SQL solution for this would be:

with recursive date_list (the_date) as (
    values ( date '2005-12-28' )
    union all
    select cast(p.the_date + interval '1' day as date)
    from date_list p
    where p.the_date <= date '2006-12-30
)
select * 
from date_list;