Search code examples
sqldatediff

Convert datetime to different format

I am using the datediff function and it doesn't seem to be working. It just gives an empty result in the data extension.

What I am currently using

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

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

So when doing this online on w3schools it seems to work:

SELECT DATEDIFF(year, PARSE(‘Monday, December 05, 2016 12:00 AM’ AS datetime), SYSDATETIMEOFFSET()) AS DateDiff;

But when using the parse function with creation_date__c it gives the following error: An error occurred while checking the query syntax. Errors: Argument data type datetime is invalid for argument 1 of parse function.

Any idea on how to parse it or what I might be doing wrong?

Solution

  • I believe you are trying to Parse a datetime to a datetime, please see the fix below. In SSMS I get the same output from both statements. To use PARSE(variable as DATETIME) you need to have a variable that is of a type that can be converted to DATETIME.

    DECLARE @creation_date__c  VARCHAR(MAX) = 'Monday, December 05, 2016 12:00 AM' ; 

SELECT DATEDIFF(year, PARSE(@creation_date__c as datetime), SYSDATETIMEOFFSET()) AS 
DateDiff