Get all dates between two dates in SQL Server

Question

How to get all the dates between two dates  I have a variable  MAXDATE which is storing the maximum date from the table  Now I want to get the all dates between  Maxdate and GETDATE   and want to store these dates in a cursor  So far I have done as follows   with GetDates As           select DATEADD day 1  maxDate  as TheDate     UNION ALL       select DATEADD day 1  TheDate  from GetDates       where TheDate  lt  GETDATE          This is working perfectly but when I am trying to store these values in a cursor SET  DateCurSor   CURSOR FOR                 SELECT TheDate                 FROM GetDates  Compilation Error  Incorrect syntax near the keyword  SET    How to solve this

User · Answer

This can be considered as bit tricky way as in my situation, I can't use a CTE table, so decided to join with sys.all_objects and then created row numbers and added that to start date till it reached the end date.

See the code below where I generated all dates in Jul 2018. Replace hard coded dates with your own variables (tested in SQL Server 2016):

select top (datediff(dd, '2018-06-30', '2018-07-31')) ROW_NUMBER() 
over(order by a.name) as SiNo, 
Dateadd(dd, ROW_NUMBER() over(order by a.name) , '2018-06-30') as Dt from sys.all_objects a

User · Answer

You can use this script to find dates between two dates  Reference taken from this Article    DECLARE  StartDateTime DATETIME DECLARE  EndDateTime DATETIME  SET  StartDateTime    2015-01-01  SET  EndDateTime    2015-01-12    WITH DateRange DateData  AS        SELECT  StartDateTime as Date     UNION ALL     SELECT DATEADD d 1 DateData      FROM DateRange      WHERE DateData  lt   EndDateTime   SELECT DateData FROM DateRange OPTION  MAXRECURSION 0  GO

User · Answer

create procedure  dbo   p display dates   startdate datetime  enddate datetime  as begin     declare  mxdate datetime     declare  indate datetime     create table  daterange  dater datetime      insert into  daterange values   startdate      set  mxdate    select MAX dater  from  daterange      while  mxdate  lt   enddate         begin             set  indate   dateadd day 1  mxdate              insert into  daterange values   indate              set  mxdate    select MAX dater  from  daterange          end     select   from  daterange end

User · Answer

You can try this       SET LANGUAGE SPANISH  DECLARE  startDate DATE   GETDATE   -- Your start date DECLARE  endDate DATE   DATEADD MONTH  16  GETDATE    -- Your end date DECLARE  years INT   YEAR  endDate  - YEAR  startDate   CREATE TABLE  TMP YEARS        year  INT    -- Get all posible years between the start and end date WHILE  years  gt   0 BEGIN     INSERT INTO  TMP YEARS       year       SELECT YEAR  startDate     years      SET  years    years - 1 END   WITH  days   day   AS -- Posible days at a month       SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL -- days lower than 10     SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL -- days lower than 20     SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL -- days lower than 30     SELECT 30 UNION ALL SELECT 31 -- days higher 30     months   month   AS -- All months at a year       SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12   SELECT CONVERT VARCHAR  a  year      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  n  month       CONVERT VARCHAR  n  month      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  d  day       CONVERT VARCHAR  d  day   as  date    FROM  TMP YEARS a  CROSS JOIN  months  n -- Join all years with all months  INNER JOIN  days  d on DAY EOMONTH CONVERT VARCHAR  a  year      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  n  month       CONVERT VARCHAR  n  month      -    CONVERT VARCHAR  DAY EOMONTH CAST CONVERT VARCHAR  a  year      -    CONVERT varchar  n  month      -15  AS DATE        gt   d  day  AND -- The number of the day can t be higher than the last day of the current month and the current year                       CONVERT VARCHAR  a  year      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  n  month       CONVERT VARCHAR  n  month      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  d  day       CONVERT VARCHAR  d  day    lt   ISNULL  endDate  GETDATE    AND -- The current date can t be higher than the end date                       CONVERT VARCHAR  a  year      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  n  month       CONVERT VARCHAR  n  month      -    REPLICATE  0   2 - LEN CONVERT VARCHAR  d  day       CONVERT VARCHAR  d  day    gt   ISNULL  startDate  GETDATE    -- The current date should be higher than the start date  ORDER BY a  year  ASC  n  month  ASC  d  day  ASC   The output will be something like this  you can format the date as you like   2019-01-24 2019-01-25 2019-01-26 2019-01-27 2019-01-28 2019-01-29 2019-01-30 2019-01-31 2019-02-01 2019-02-02 2019-02-03 2019-02-04 2019-02-05 2019-02-06 2019-02-07 2019-02-08 2019-02-09

User · Answer

Easily create a Table Value Function that will return a table with all dates  Input dates as string You can customize the date in the the format you like  01 01 2017  or  01-01-2017  in string formats  103 126       Try this  CREATE FUNCTION  dbo   DateRange To Table     minDate Str NVARCHAR 30    maxDate Str NVARCHAR 30    RETURNS   Result TABLE DateString NVARCHAR 30  NOT NULL  DateNameString NVARCHAR 30  NOT NULL   AS  begin      DECLARE  minDate DATETIME   maxDate DATETIME     SET  minDate   CONVERT Datetime   minDate Str 103      SET  maxDate   CONVERT Datetime   maxDate Str 103        INSERT INTO  Result DateString  DateNameString       SELECT CONVERT NVARCHAR 10   minDate 103   CONVERT NVARCHAR 30  DATENAME dw  minDate          WHILE  maxDate  gt   minDate     BEGIN         SET  minDate    SELECT DATEADD dd 1  minDate           INSERT INTO  Result DateString  DateNameString           SELECT CONVERT NVARCHAR 10   minDate 103   CONVERT NVARCHAR 30  DATENAME dw  minDate       END         return  end      To execute the function do this   SELECT   FROM dbo DateRange To Table   01 01 2017   31 01 2017     The output will be  01 01 2017  Sunday 02 01 2017  Monday 03 01 2017  Tuesday 04 01 2017  Wednesday 05 01 2017  Thursday 06 01 2017  Friday 07 01 2017  Saturday 08 01 2017  Sunday 09 01 2017  Monday 10 01 2017  Tuesday 11 01 2017  Wednesday 12 01 2017  Thursday 13 01 2017  Friday 14 01 2017  Saturday 15 01 2017  Sunday 16 01 2017  Monday 17 01 2017  Tuesday 18 01 2017  Wednesday 19 01 2017  Thursday 20 01 2017  Friday 21 01 2017  Saturday 22 01 2017  Sunday 23 01 2017  Monday 24 01 2017  Tuesday 25 01 2017  Wednesday 26 01 2017  Thursday 27 01 2017  Friday 28 01 2017  Saturday 29 01 2017  Sunday 30 01 2017  Monday 31 01 2017  Tuesday

User · Answer

DECLARE  FirstDate DATE    2018-01-01  DECLARE  LastDate Date    2018-12-31  DECLARE  tbl TABLE ID INT IDENTITY 1 1  PRIMARY KEY CurrDate date  INSERT  tbl VALUES   FirstDate  WHILE  FirstDate  lt   LastDate BEGIN SET  FirstDate   DATEADD  day 1   FirstDate  INSERT  tbl VALUES   FirstDate  END INSERT  tbl VALUES   LastDate    SELECT   FROM  tbl

User · Answer

Just saying   here is a more simple approach to this  declare  sdate date    2017-06-25         edate date    2017-07-24    with dates CTE  date  as       select  sdate      Union ALL     select DATEADD day  1  date      from dates CTE     where date  lt   edate   select   from dates CTE

User · Answer

My first suggestion would be use your calendar table  if you don t have one  then create one  They are very useful  Your query is then as simple as   DECLARE  MinDate DATE    20140101            MaxDate DATE    20140106    SELECT  Date FROM    dbo Calendar WHERE   Date  gt    MinDate AND     Date  lt   MaxDate    If you don t want to  or can t create a calendar table you can still do this on the fly without a recursive CTE   DECLARE  MinDate DATE    20140101            MaxDate DATE    20140106    SELECT  TOP  DATEDIFF DAY   MinDate   MaxDate    1          Date   DATEADD DAY  ROW NUMBER   OVER ORDER BY a object id  - 1   MinDate  FROM    sys all objects a         CROSS JOIN sys all objects b    For further reading on this see    Generate a set or sequence without loops     part 1 Generate a set or sequence without loops     part 2 Generate a set or sequence without loops     part 3   With regard to then using this sequence of dates in a cursor  I would really recommend you find another way  There is usually a set based alternative that will perform much better   So with your data     date     it cd   qty  24-04-14    i-1    10  26-04-14    i-1    20   To get the quantity on 28-04-2014  which I gather is your requirement   you don t actually need any of the above  you can simply use   SELECT  TOP 1 date  it cd  qty  FROM    T WHERE   it cd    i-1  AND     Date  lt    20140428  ORDER BY Date DESC    If you don t want it for a particular item   SELECT  date  it cd  qty  FROM        SELECT  date                       it cd                       qty                       RowNumber   ROW NUMBER   OVER PARTITION BY ic id                                                      ORDER BY date DESC              FROM    T             WHERE   Date   lt    20140428            T WHERE   RowNumber   1

User · Answer

You can use SQL Server recursive CTE DECLARE       MinDate DATE    2020-01-01        MaxDate DATE    2020-02-01    WITH Dates day  AS        SELECT CAST  MinDate as Date  as day     UNION ALL     SELECT CAST DATEADD day  1  day  as Date  as day     FROM Dates     WHERE CAST DATEADD day  1  day  as Date   lt   MaxDate   SELECT  FROM dates

User · Answer

This is the method that I would use   DECLARE       DateFrom DATETIME   GETDATE         DateTo DATETIME   DATEADD HOUR  -1  GETDATE     2   -- Add 2 days and minus one hour   -- Dates spaced a day apart   WITH MyDates  MyDate  AS       SELECT  DateFrom     UNION ALL     SELECT DATEADD DAY  1  MyDate      FROM MyDates     WHERE MyDate  lt   DateTo       SELECT      MyDates MyDate       CONVERT DATE  MyDates MyDate  AS  MyDate in DATE format  FROM      MyDates    Here is a similar example  but this time the dates are spaced one hour apart to further aid understanding of how the query works   -- Alternative example with dates spaced an hour apart  WITH MyDates  MyDate  AS  SELECT  DateFrom     UNION ALL     SELECT DATEADD HOUR  1  MyDate      FROM MyDates     WHERE MyDate  lt   DateTo       SELECT      MyDates MyDate FROM      MyDates    As you can see  the query is fast  accurate and versatile

User · Answer

I listed dates of 2 Weeks later  You can use variable  period OR function datediff dd   date start   date end   declare  period INT   date start datetime   date end datetime   i int   set  period   14 set  date start   convert date DATEADD D  - period  curent timestamp   set  date end   convert date current timestamp  set  i   1  create table  datesList dts datetime  insert into  datesList values   date start  while  i  lt    period     Begin         insert into  datesList values  dateadd d  i  date start           set  i    i   1     end select cast dts as DATE  from  datesList Drop Table  datesList

[sql] Get all dates between two dates in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to cursor