GROUP by CONVERT(varchar(10),A. ON A.sDate = CONVERT(varchar(10),B.call_time,111) SELECT DATEADD(day,1,sDate) From cte where DATEADD(day,1,sDate) <= CONVERT(varchar(10),A.sDate,111) AS OriginalDate, COUNT(B.call_time) as total Now this CTE is will be refactored to make a sub query with LEFT OUTER JOIN so that the date which does not have the value appears and contains 0 value. It can refer to itself also which is called recursive CTE.įirst, we will write a CTE which will generate all the dates within the date range. Let’s explain step-by-step.ĬTE or Common Table Expression specifies a temporary named result set which is derived from a simple query and defined within the execution scope of a single SELECT/INSERT/UPDATE/DELETE/MERGE/CREATE VIEW statement. Recursive CTE is used to generate the date range and LEFT OUTER JOIN is used to combine the value with the date. Rather than using simple GROUP BY query, CTE and SUB QUERY are used. ORDER BY CONVERT(varchar(10),B.call_time,111) GROUP BY CONVERT(varchar(10),B.call_time,111) SELECT CONVERT(varchar(10),B.call_time,111) AS OriginalDate, COUNT(*) as total Desired output is, missing dates will be 0 value. If we run the GROUP BY statement on datetime column, some days will be missing. Let’s say we have a table which contains incoming call records of a customer care from 1st to 10th June, 2021.

DATEADD () Adds or subtracts a specified time interval from a date.

DATEPART () Returns a single part of a date/time. GETDATE () Returns the current date and time. So, I compiled my own using recursive CTE or Common Table Expression. The following table lists the most important built-in date functions in SQL Server: Function. I came across couple of solutions, neither seemed convenient to me. Last week one of my colleagues requested me to help him write a query to fill in missing dates in query output.

Sql server date query