I need to check in a table, is the all day data available, here is the handy script to check the same..
Adding here for quick reference and to share with colleagues.
DECLARE @startdate DATETIME , @enddate DATETIME; SET @startdate = '07/01/2018'; SET @enddate = '08/04/2018'; WITH CalendarDates AS ( SELECT dt = @startdate UNION ALL SELECT DATEADD(DAY, 1, dt) FROM calendardates WHERE DATEADD(DAY, 1, dt) <= @enddate ), Summary AS( SELECT DISTINCT dt, ReportUTCDate FROM tblData FULL OUTER JOIN CalendarDates ON dt = ReportUTCDate WHERE ReportUTCDate IS NULL ) SELECT * FROM Summary
Adding here for quick reference and to share with colleagues.