Search This Blog

23 September 2010

Writing a recursive query to find out all dates between two given date

--Declaring the required variables
DECLARE @fromdate DATETIME
DECLARE @todate DATETIME
SET @fromdate = '2010-01-01'
SET @todate = '2010-01-10'

--Query to fetch all the dates between two given date
;WITH tmpinfo(tmpdt) AS
(SELECT @fromdate
UNION ALL
SELECT tmpdt + 1
FROM tmpinfo
WHERE tmpdt < @todate)
SELECT * FROM tmpinfo OPTION (MAXRECURSION 0);

12 comments:

  1. Thank you! It is really useful

    ReplyDelete
  2. Good. Found a simple and useful query here

    ReplyDelete
  3. MySQL reports about syntax error:
    [ERROR in query 1] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--Declaring the required variables
    DECLARE @fromdate DATETIME
    DECLARE @todate ' at line 1
    [ERROR in query 2] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH tmpinfo(tmpdt) AS
    (SELECT @fromdate
    UNION ALL
    SELECT tmpdt + 1
    FROM tmp' at line 1

    ReplyDelete
  4. Well this query is for SQL server not MySQL

    ReplyDelete
  5. PERFECT! I have been looking for something like this for days.

    ReplyDelete
  6. This is symply what I was looking for. THANK YOU!!!

    ReplyDelete
  7. You have a spelling error in your page title. Should be Technology, not Techonology.

    ReplyDelete
  8. Awesome. I was searching for something like this.

    ReplyDelete