1

I am unable to filter by year in SQL.

This is my SQL:

SELECT
c.Organisation__c,
DATEPART(year, s.ContractYear__c) AS ContractYear__c
FROM ....

WHERE DATEPART(year, s.ContractYear__c) = '2020'

and it's pulling all records, I can see various years in the ContractYear__c column:

enter image description here

How to correctly filter by year? Thank you

2
  • What's the datatype of the ContractYear__c column? – Adam Spriggs Feb 19 at 15:23
  • It's Date datatype – julia2019 Feb 19 at 17:04
1

How about if you use the year() function and compare it to a number?

SELECT
c.Organisation__c,
year(s.ContractYear__c) as ContractYear__c
FROM ....
WHERE year(s.ContractYear__c) = 2020
2
  • the result is the same as with DATEPART, still doesn't filter the results by year – julia2019 Feb 22 at 10:15
  • OK, I noticed that it's working when I do WHERE year(s.ContractYear__c) = 2020 AND c.Email IS NOT NULL but it don't work when I do WHERE c.Email IS NOT NULL AND year(s.ContractYear__c) = 2020 – julia2019 Feb 22 at 10:22
1

The reason for the problem is in = '2020'. DATEPART function returns an integer, so it is better to compare to = 2020.

YEAR function by itself shouldn't change anything as it is just wrapper on DATEPART(YEAR, X).

This should work:

SELECT
    c.Organisation__c,
    DATEPART(YEAR, s.ContractYear__c) AS ContractYear__c
FROM ....
WHERE DATEPART(YEAR, s.ContractYear__c) = 2020

However, I also tested it with a string date on my instance and it was working correctly for the DateJoined field of _Subscribers data view.

Are you testing the query with Query Studio or SQL Query Activity? If the latter, are you cleaning the Data Extension between tests? Maybe those are records from previous runs with the new ones being added/updated?

1
  • I tried with '2020' and with 2020 and it still doesn't filter correctly – julia2019 Feb 22 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.