Ask Your Question

Revision history  [back]

Revision 2
Ahmed Qasid's avatar
5.8k
Ahmed Qasid
updated 2018-07-04 13:21:14 +0200

The following query gets all dates between two dates as a table:

with dates as (
SELECT  TOP (DATEDIFF(DAY, '20160101', '20161231') + 1)
        cast(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, '20160101') as date) dayDate
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
)

select * from dates

Revision 1
Eldabbas's avatar
9.1k
Eldabbas
answered 2018-07-04 06:20:35 +0200

The following query gets all dates between two dates as a table:

 
with dates as (
SELECT  TOP (DATEDIFF(DAY, '20160101', '20161231') + 1)
        cast(DATEADD(DAY, ROW_NUMBER() ROWNUMBER() OVER(ORDER BY a.object_id) a.objectid) - 1, '20160101') as date) dayDate
FROM    sys.all_objects sys.allobjects a
        CROSS JOIN sys.all_objects sys.allobjects b
)
)
  
select * from dates