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

  • What the heck does 'ALFKI' mean?
  • SQL Server makes up extra precision for floats?
  • How do i rename the table name using SQL query?
  • SQL Azure V12 BACPAC import error: “The internal target platform type SqlAzureV12DatabaseSchemaProvider does not support schema file version '3.3'”
  • SQL Server - Prevent lost update and deadlocks
  • SQL update query and 'subquery returned more than one value'
  • 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)
    ...
    

    bad-habits-to-kick-using-shorthand-with-date-time-operations

    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 ;
    

    Changes:

    • The dates for comparison are in a standard format, so they should be interpreted correctly.
    • The GROUP BY uses the same structure as the SELECT for the columns.
    • The SUM(CASE) is replaced by the much simpler COUNT().
    • The value for CreatedDate is cast as a DATE. To be honest, I’m not sure that will fix any problem, because that should be happening anyway.
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.