Datepart Calculation in Where clause
I am having problem with the datepart calculation in the Where clause of a query. The query returns result without the calculation but nothing if i add the condition.
DECLARE @StatusId INT; SELECT @StatusId = Id FROM company.Status WHERE Name = 'Signed' AND CompanyId = 1; SELECT FORMAT(CAST(cont.CreatedDate AS DATE), 'MM/dd') AS newDate, SUM(CASE WHEN cont.UpdatedDate IS NOT NULL THEN 1 ELSE 0 END) AS TotalSignedLeads FROM client.testw cont WHERE cont.CompanyId = 1 AND cont.AffiliateId = 1 AND cont.CreatedDate BETWEEN '7-01-2017' AND '7-09-2017' AND DATEPART(dw, cont.CreatedDate) NOT IN (1, 7) //This causes problem. AND cont.StatusId = @StatusId GROUP BY CAST(cont.CreatedDate AS DATE) ORDER BY newDate ; This is the data above query gives without the datepart condition. newDate TotalSignedLeads 07/08 7
2 Solutions collect form web for “Datepart Calculation in Where clause”
Well since you are casting
cont.CreatedDate as a date in the top of the query, I suspect it’s actually a
varchar… thus you need
... and datepart(weekday, cast(cont.CreatedDate as date)) not in (1,7) ...
If you aren’t getting an error, then you don’t have any rows which meet that condition. Perhaps your DATEFIRST setting isn’t what you think it is.
Also, not sure what
GROUP BY CAST(@StatusId.CreatedDate AS DATE) is meant to be…
I suspect that you want a query more like this:
SELECT FORMAT(CAST(cont.CreatedDate AS DATE), 'MM/dd') AS newDate, COUNT(cont.UpdatedDate) AS TotalSignedLeads FROM client.testw cont WHERE cont.CompanyId = 1 AND cont.AffiliateId = 1 AND cont.CreatedDate BETWEEN '2017-07-01' AND '2017-09-01' AND DATEPART(dw, CAST(cont.CreatedDate AS DATE)) NOT IN (1, 7) AND //This causes problem. cont.StatusId = @StatusId GROUP BY FORMAT(CAST(cont.CreatedDate AS DATE), 'MM/dd') ORDER BY newDate ;
- The dates for comparison are in a standard format, so they should be interpreted correctly.
GROUP BYuses the same structure as the
SELECTfor the columns.
SUM(CASE)is replaced by the much simpler
- The value for
CreatedDateis cast as a
DATE. To be honest, I’m not sure that will fix any problem, because that should be happening anyway.