0

I am using the datediff function and it doesn't seem to be working.

What I am currently using

DATEDIFF(year, creation_date__c, SYSDATETIMEOFFSET()) AS AnniversaryYears

It just gives an empty result in the data extension for AnniversaryYears.

creation_date__c is of the following format: Monday, December 05, 2016 12:00 AM

Any idea what I might be doing wrong?

Improve this question
  • 2
    in your DE, is creation_date__c a Date field or a text field? – zuzannamj Dec 5 '19 at 13:47
2

If your field creation_date__c is of the correct type, date, this should fulfil your use case:

SELECT
DATEDIFF(year, creation_date__c, getdate()) AS AnniversaryYears
FROM
DataExtensionName

Result:

enter image description here

The difference between GETDATE() and SYSDATETIMEOFFSET() is that the latter will just add the time zone, which won't be comparable to creation_date__c since it doesn't have a timezone.

Improve this answer
  • It is of the datetime type, so your solution is also not working :/ – user3485470 Dec 5 '19 at 14:02
  • sure, but what do you see in the Data Extension properties for that field? Does it say Date or Text or something else? – zuzannamj Dec 5 '19 at 14:12
  • Date, like you show in the screenshot. My bad. – user3485470 Dec 5 '19 at 14:13
  • 1
    @user3485470 Can you post your full query into your question, it may be a syntax issue outside of this snippet that is causing the issue. – Gortonington Dec 5 '19 at 14:28
  • I was mapping it to the wrong attribute. I had AS AnniversaryYears while the attribute was called YearsAnniversary. Ima cry myself to sleep. Thanks anyways! Would have expected an error message for that invalid mapping though. – user3485470 Dec 5 '19 at 14:36
4

The issue might be that the date format of the two is not matching correctly.

Try using:
DATEDIFF(year, CAST(creation_date__c as DATE), CAST(getdate() as DATE))

So that you cast each as identical data types, which should remove any possible data mismatch.

Improve this answer

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.