CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-server-2005
Ben
Ben

Reputation: 11208

Struggling with faulty date fields in SQL Server

I have two tables, a parent table and a child-table. The child table is a vertical designed table (meaning it stores and Id, ParentId, Property and PropertyValue). Naturally the PropertyValue can hold all types of data.

I'm trying to filter this set but I'm struggling with faulty dates and empty fields. I'm unable to create functions due to read-only access so I have to do everything in the actual query. I tried using a subquery but I'm experiencing I'm not getting the results from the subquery to work with in the outer query.

So far I've got this:

DECLARE @Year Int 
SET @Year = 2015

SELECT
    COUNT(Parent.ID), YEAR(PropertyValue), MONTH(PropertyValue)
FROM        
    Parent
INNER JOIN  
    Child ON Parent.ID = Child.ParentID
WHERE       
    Parent.ID IN (SELECT ParentID
                  FROM Child
                  WHERE Child.Property = 'MyDateField'
                    AND ISDATE(Child.PropertyValue) = 1)
    AND Child.Property = 'MyDateField'
    AND YEAR(Child.PropertyValue) = @Year
GROUP BY 
    YEAR(Child.PropertyValue), MONTH(Child.PropertyValue)

Any suggestions on how to cut out the faulty date rows and proceed with the desired dataset?

Upvotes: 0

Views: 57

Answers (3)

Ben
Ben

Reputation: 11208

I would like to thank Jesus Lopez, Harsch and t-clausen.dk for thinking with me. While the above approaches did eliminate the SQL-error it didn't provide me with the results I was expecting. I believe this is due to the behavior of the ISDATE() function and the way the dates are saved (as text dd-mm-yyyy). I've noticed that when using ISDATE() all the results after the 12th day in a month were not there (I think this is because ISDATE interprets the text-format wrong)

I tried a different approach, using common table expressions. The query below works like a charm.

DECLARE @Year int
SET @Year = 2015;

WITH FilteredResults (ParentId, DateValue) 
AS (
    SELECT  ParentId, CONVERT(DateTime, PropertyValue, 105)
    FROM    Child
    WHERE   Property = 'MyDateField'
    AND     PropertyValue <> ''
)

SELECT   count(*), year(DateValue), month(DateValue) 
FROM     FilteredResults 
WHERE    year(DateValue) = @Year
GROUP BY year(DateValue), month(DateValue)
ORDER BY year(DateValue), month(DateValue)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

Try this instead. Everything else seems redundant. You don't need to check the dates in the SELECT- or GROUP BY part. Your IN statement is already included in the WHERE clause.

SELECT
    COUNT(Parent.ID), 
    YEAR(Child.PropertyValue), 
    MONTH(Child.PropertyValue)
FROM        
    Parent
INNER JOIN  
    Child ON Parent.ID = Child.ParentID
WHERE       
    Child.Property = 'MyDateField'
    AND CASE WHEN ISDATE(Child.PropertyValue) = 1 
      THEN YEAR(Child.PropertyValue) END = @Year
GROUP BY 
    YEAR(Child.PropertyValue), MONTH(Child.PropertyValue)

Upvotes: 2

Jes&#250;s L&#243;pez
Jes&#250;s L&#243;pez

Reputation: 9241

Replace Child.PropertyValue with the following expression:

(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END)

EDIT: Here you have the query:

SELECT      COUNT(Parent.ID), YEAR(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END), MONTH(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END)
FROM        Parent
INNER JOIN  Child
ON          Parent.ID = Child.ParentID
WHERE       Parent.ID IN (
    SELECT ParentID
    FROM    Child
    WHERE   Child.Property = 'MyDateField'
    AND ISDATE(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END) = 1
)
AND Child.Property = 'MyDateField'
AND YEAR(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END) = @Year
GROUP BY YEAR(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END), MONTH(CASE WHEN ISDATE(Child.PropertyValue) = 1 THEN CAST(Child.PropertyValue AS datetime) ELSE NULL END)

Upvotes: 1

Related Questions