CODEWITHSUNDEEP
sqlsql-server
Mr 2017
Mr 2017

Reputation: 113

SQL query returning blank results

When I run the query below, it returns no data! Does anyone know why that may be? 

select 
    es.SubCategoryName,
    es.ClientProductID,
    es.ProductSID,
    CAST (ps.Date as date) as Date,
    Price as Price, 
    Cost as Cost 
from 
    ProductStatus as ps
join 
    v_EnterpriseStructure as es on es.ProductSID = ps.ProductSID
join 
    PriceZone as pz on pz.PriceZoneID = ps.PriceZoneID
where
    es.ProductSID = '9577C393-833B-4ACB-9A8E-8B748C65A488'
    and ps.Date = '2014-12-17' 
group by
    es.SubCategoryName,
    es.ClientProductID, es.ProductSID,
    ps.Date, ps.Price, ps.Cost

I know if you forget to put inverted commas around a date, you get blank results.

But I'm not sure why it would happen here?

I'm using SQL Server Management Studio 2014.

TIA.

Upvotes: 2

Views: 4135

Answers (4)

DarthTommy
DarthTommy

Reputation: 457

Since you did not provide any information about your table, I am going to assume that the Data Type of the ProductStatus.Date column is 'datetime.' So I believe that your issue is in your WHERE clause. Try changing to this:

WHERE 
        es.ProductSID = '9577C393-833B-4ACB-9A8E-8B748C65A488'
        and year(ps.Date) = '2014' and month(ps.Date) = '12' and day(ps.Date) = '17'

Hope this helps

Upvotes: 0

Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2725

You should try casting your date column to date in the WHERE statement. If it is stored as a datetime it won't end up matching. 

 SELECT 
 es.SubCategoryName    AS SubCategoryName
,es.ClientProductID    AS ClientProductID 
,es.ProductSID         AS ProductSID
,CAST(ps.DATE AS DATE) AS DATE
,Price                 AS Price
,Cost                  AS Cost
FROM 
           ProductStatus         AS ps
INNER JOIN v_EnterpriseStructure AS es ON es.ProductSID  = ps.ProductSID
INNER JOIN PriceZone             AS pz ON pz.PriceZoneID = ps.PriceZoneID
WHERE 
    es.ProductSID         = '9577C393-833B-4ACB-9A8E-8B748C65A488'
AND CAST(ps.DATE AS DATE) = CAST('2014-12-17' AS date)

GROUP BY es.SubCategoryName
,es.ClientProductID
,es.ProductSID
,ps.DATE
,ps.Price
,ps.Cost;

I would give this a try. I am guessing it has to do with the type of columns sql is storing the data as.

Upvotes: 1

JonTout
JonTout

Reputation: 640

You added CAST (ps.Date as date) into your Select statement, it would probably be prudent to use a similar format in your where "date" clause, but you should really post some of your data.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

That would happen because 2014-12-17 evaluates to 1985. This is an integer, not a date.

Presumably, no dates match 1985. SQL Server will attempt to convert that value to a date, which is something like '1905-09-06'.

Upvotes: 0

Related Questions