1

I need to get all dates from 01-01-2016 to todays date.

Example:

01-01-2016
02-01-2016
03-01-2016
.
.
.
01-02-2016
.
.
.
17-11-2017

Any help would be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kalyan
  • 17
  • 6
  • 2
    Search for `calendar table`, there are many examples online – HoneyBadger Nov 17 '17 at 09:32
  • 1
    I don't think you make some research before asking the question. https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ – Ilyes Nov 17 '17 at 09:34
  • 2
    Possible duplicate of [How to create a Calendar table for 100 years in Sql](https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql) – Ilyes Nov 17 '17 at 09:38
  • please have a look at [how-to-ask](http://stackoverflow.com/help/how-to-ask) – swe Nov 17 '17 at 10:00

4 Answers4

2

you can make use of common table expression for achieving this and it is one of the fastest way to do it

;WITH cte 
 as
(
  SELECT CONVERT(DATE,'01-01-2016') as DateCol
  UNION ALL
  SELECT CONVERT(DATE,DATEADD(DAY,1,DateCol))  FROM CTE
  WHERE DATEADD(DAY,1,DateCol) <= '11-17-2017'
 )

SELECT * FROM CTE 
OPTION (maxrecursion 0)
Aswani Madhavan
  • 816
  • 6
  • 19
  • This is the method I use more often than not, however be aware that iterative ctes tend not to be very efficient. I've used the approach above to populate a table of dates, and then a job to refresh at midnight every night. May seem like overkill, but depends on how often you hit the query. – Matthew Baker Nov 17 '17 at 11:38
0
 SELECT *FROM Table_Name 
 WHERE CONVERT(NVARCHAR,date_column,111) >=CONVERT(NVARCHAR,@From_Date,111) and  CONVERT(NVARCHAR,date_column,111) <=CONVERT( NVarchar, @TO_Date, 111)
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17
0

You need to have a numbers table for this to work..

select dateadd(day,Number,'01-01-2016') from numbers
where 
dateadd(day,Number,'01-01-2016')<=getdate()

To create numbers table

USE Model
GO

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

References:
http://dataeducation.com/you-require-a-numbers-table/

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

This is what i have understand from your requirement. Check below query.

=========================================

declare @date datetime='2016-01-01'

declare @tbl table (alldate date)

while (@date<=GETDATE()) begin insert into @tbl select @date

set @date=@date+1

if @date=GETDATE() begin break; end end

select * from @tbl

Manish Kumar
  • 16
  • 2