Find Missing date Query

Find Missing date Query

Ø  Source table

  select Distinct  CONVERT (DATE, EntDt) as dt from  dbo.K_RPT_EMAIL_STATUS

  order by CONVERT (DATE, EntDt)  asc

Ø  Create Function

Create FUNCTION [dbo].[GetAllDaysInBetween](@FirstDayDATETIME, @LastDay DATETIME)

RETURNS @retDays TABLE

(

    DayInBetween DATETIME

)

AS

BEGIN

    DECLARE @currentDay DATETIME

    SELECT @currentDay = @FirstDay

    WHILE @currentDay <= @LastDay

    BEGIN

        INSERT @retDays (DayInBetween)

            SELECT @currentDay

        SELECT @currentDay = DATEADD(DAY, 1,@currentDay)

    END

    RETURN

END

Ø  Final Query

SELECT  DayInBetween AS missingDate

       FROM dbo.GetAllDaysInBetween('2014-11-25', '2015-11-24') AS AllDaysInBetween

  WHERE NOT EXISTS

      (select Distinct  CONVERT (DATE, EntDt) as dt from  dbo.K_RPT_EMAIL_STATUS

        WHERE CONVERT (DATE, EntDt)  =AllDaysInBetween.DayInBetween  )