SQL Server - WHERE Date Range & GROUP BY MonthName

Question

I have 2 same queries (to return "MonthName Year" and count) as below, but only the date range in the WHERE condition is different. Query 1 gets only the June month count, while Query 2 gets count from Apr to Jul, where the Jun month count (in Query 2) is not same as June month count from Query 1. Please advise.

Query 1:

SELECT DATENAME(MONTH, SubmissionDate) + ' ' + DateName(Year, SubmissionDate) AS MonthNumber, COUNT(1) AS InquiryCount 
, Cast(Datename(MONTH,SubmissionDate) + ' ' + Datename(YEAR,SubmissionDate) AS DATETIME) AS tmp
    FROM [dbo].[InvestigationDetails] (nolock)
    WHERE SubmissionDate>= '06/01/2016' 
    AND SubmissionDate <= '06/30/2016'
    GROUP BY DATENAME(MONTH, SubmissionDate) + ' ' + DateName(Year, SubmissionDate), DateName(Year, SubmissionDate)
    ORDER BY tmp ASC

Query 2:

SELECT DATENAME(MONTH, SubmissionDate) + ' ' + DateName(Year, SubmissionDate) AS MonthNumber, DateName(Year, SubmissionDate), COUNT(1) AS InquiryCount 
, Cast(Datename(MONTH,SubmissionDate) + ' ' + Datename(YEAR,SubmissionDate) AS DATETIME) AS tmp
    FROM [dbo].[InvestigationDetails] (nolock)
    WHERE SubmissionDate>= '04/01/2016' 
    AND SubmissionDate <= '07/31/2016'
    GROUP BY DATENAME(MONTH, SubmissionDate) + ' ' + DateName(Year, SubmissionDate), DateName(Year, SubmissionDate)
    ORDER BY tmp ASC

Thanks, Jay

How is the June data in the first query different from the second? — Tyler Roper, Aug 03 '16 at 00:35
Query 1 returns: June 2016 - 290 & Query 2 returns the count 307 for June 2016. Please advise. — Jay, Aug 03 '16 at 00:47
Need the data to play with and see whats going on but to troubleshoot it try to change your where clause in the 1st query to: where DATENAME(MONTH, SubmissionDate) = 'June' .... see if that still gives you the same result as before. Also is SubmissionDate a DateTime field or varchar ? — objectNotFound, Aug 03 '16 at 03:00

score 1 · Answer 1 · answered Aug 03 '16 at 19:13

SubmissionDate must be of type DATETIMEand thus, you are missing all values for your last day, 06/30/2016, since this equates to 06/30/2016 00:00:00. This means any records that have SubmissionDate with a time > 00:00:00 on 6/30/2016 will be excluded. For example, 6/30/2016 12:44:22 wouldn't be included in your results with your current logic.

Use one of these instead:

AND SubmissionDate < '07/01/2016'
AND SubmissionDate <= '06/30/2016 23:59:59.999'

The first method is preferred since you will get all records before 7/1/2016, which includes 6/30/2016 23:59:59.999. Of course, you should be aware of how precise DATETIME can be in SQL Server. Run the code below to see what I mean.

 declare @dt datetime2 = getdate()
 select @dt         --more precise with datetime2
 select getdate()   --not as precise

SQL Server - WHERE Date Range & GROUP BY MonthName

1 Answers1