'Marketing Cloud SQL converting datetime to date field for dateadd function

I'm using SQL server in Marketing Cloud to write a data extension that will allow me to report on Salesforce Opportunities that have a closedate within 90 days of an email's open date.

I selected all the fields I need to be able to run the reporting in Excel, and joined my datasets together.

I have a where clause in the query:

and o.closedate < dateadd(day, 91, 'e.et4ae5__DateOpened__c')

The SQL validates, but I get error:

Could not convert date and/or time from string data type.

closedate is a date field format yyyy-mm-dd, where dateopened is a datetime field. Do I need to convert the datetime to a date field before using it in the dateadd function?

I tried adding to the select statement: convert('e.et4ae5__DateOpened__c', getdate(), 23) and I'm getting error:

Incorrect syntax near 'e.et4ae5__DateOpened__c'.

What am I doing wrong?

sqlsql-serversalesforce-marketing-cloud


Solution 1:[1]

'e.et4ae5__DateOpened__c' is a string and SQL Server (just like me) is not understanding what this could mean as date.

It might be a column identifier, though. e being the alias and et4ae5__DateOpened__c being the column name.

Solution 2:[2]

Solved:

I changed the select statement to:

convert(date, [et4ae5__DateOpened__c], 23) as [et4ae5__DateOpened__c]

and the where clause to:

o.closedate < dateadd(day, 91, [et4ae5__DateOpened__c])

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 divisionby0
Solution 2 Dale K

Related Questions