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?
CodePudding user response:
'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.
CodePudding user response:
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])