Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CTE to get dates between two dates using SQL Server

I want to fetch missing dates between two dates

say @maxDate = '2013-01-28'

say @curDate = GetDate()

I have written CTE to do this. Here is my CTE:

create procedure EmpDate  
as  begin  
declare @curDate Date  
set @curDate = GETDATE()  
declare @maxDate Date  
select @maxDate = MAX(EmpAttendance.Sdate)  
from EmpAttendance

;with GetDates As  
(  
select 1 as counter, @maxDate as Date   
UNION ALL  
select counter + 1, DATEADD(day,counter,@maxDate)  
from GetDates  
where DATEADD(day, counter, @maxDate) < @curDate  
)  
select Date from GetDates  
end   
go  

The result of this is

Date  
2013-01-28  
2013-01-29  
2013-01-30  

but I want

2013-01-29  
2013-01-30   

Please help me out.

like image 305
user2029125 Avatar asked Jan 31 '13 13:01

user2029125


People also ask

How can I get date between two dates in SQL query?

To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference.

Can we use CTE in function in SQL Server?

CTE was introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE a view, as part of the view's SELECT query.

How do I count days between two dates in SQL Server?

SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.

What is CTE in SQL Server with example?

A common table expression, or CTE, is a temporary named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement. Each SQL CTE is like a named query, whose result is stored in a virtual table (a CTE) to be referenced later in the main query.


2 Answers

Change

select 1 as counter, @maxDate as Date

to

select 1 as counter, DATEADD(day,1,@maxDate) as Date

To make it simpler though change the CTE

;with GetDates As  
(  
select DATEADD(day,1,@maxDate) as TheDate
UNION ALL  
select DATEADD(day,1, TheDate) from GetDates  
where TheDate < @curDate  
)
... 
like image 167
gbn Avatar answered Sep 19 '22 19:09

gbn


DECLARE @STARTDATE      DATETIME;
DECLARE @ENDDATE        DATETIME;
DECLARE @YEARS          INTEGER;

SET @YEARS = 10;
SET @STARTDATE = '20170101';

SELECT  @ENDDATE = DATEADD(DAY,-1,DATEADD(YEAR,@YEARS,@STARTDATE));
DECLARE @FirstDayOfWeek INTEGER;

SET @FirstDayOfWeek = 6;

;WITH CTE_DATES
AS
(
    SELECT @STARTDATE AS [DATE],
    1 AS [Level]
    UNION ALL
    SELECT 
        DATEADD(DAY,1, [DATE] )  , [Level] + 1  
    FROM CTE_DATES
    WHERE [DATE] < @ENDDATE
)
SELECT 
    [DATE],
    DATENAME(dw,[Date]) AS Daynamelong,
    LEFT(DATENAME(dw,[Date]),3) AS DaynameShort,
    DATEPART(dw,[Date]) AS NaturalDayNumber,
    CASE WHEN DATEPART(dw,[Date]) >= @FirstDayOfWeek THEN  (DATEPART(dw,[Date]) - (@FirstDayOfWeek)) +1
    ELSE 
        ((DATEPART(dw,[Date]) - (@FirstDayOfWeek)) +1) + 7
    END AS SpecialDayNumber,
    [Level]
FROM 
    CTE_DATES 
ORDER BY
    [DATE] ASC
OPTION (MAXRECURSION 5000);
like image 34
DatabaseDave Avatar answered Sep 19 '22 19:09

DatabaseDave