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.
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.
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.
SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.
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.
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
)
...
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With