0

We are trying to convert a string field to a date field using the CAST function in Marketing Cloud. Our goal is to use the newly converted date records to segment our subscribers based on the date of purchase (last 30 days). However, we're keep getting the " Could not convert date and/or time from string data type" error in Automation Studio and Query Studio. An example of a record in the string field "Date_Dispo" is: "15/10/2019". Below's the SQL code:

SELECT Customer_Key, Email, Date_Dispo 
FROM Audience_1 
WHERE DATEDIFF(day, CAST(Date_Dispo as Date),CAST(Getdate() as date)) <= 30

Please help. Thanks

  • 1
    What date format do you have for Date_Dispo ? – Zak Ben Oct 3 '19 at 8:31
  • It's in this format: 15/10/2019 – nix9247 Oct 3 '19 at 10:56
3

I wouldn't store a date like that, but if you don't have a choice, this should do the trick:

SELECT 

Customer_Key, 
Email, 
convert(varchar, convert(date, (Date_Dispo + ' 12:00:00 AM'), 105), 101) AS Date_Dispo 

FROM Audience_1

I'm not able to try your whole query, but it should look something like this:

SELECT 

Customer_Key, 
Email, 
convert(varchar, convert(date, (Date_Dispo + ' 12:00:00 AM'), 105), 101) AS Date_Dispo 

FROM Audience_1 

WHERE DATEDIFF(day, CAST(convert(varchar, convert(date, (Date_Dispo + ' 12:00:00 AM'), 105), 101) as Date),CAST(Getdate() as date)) <= 30

The text field is concatenated with a timestamp (possibly would also work without this?) and converted to a 105 dd-mm-yyyy date format, and then converted once more to a 101 mm/dd/yyyy date format, as this is the date format used in SFMC.

Take a look at the link: https://www.w3schools.com/sql/func_sqlserver_convert.asp

EDIT

I confirm that there is no need to concat with the timestamp, so this should be the final version:

convert(varchar, convert(date, Date_Dispo, 105), 101) AS Date_Dispo 
  • Worked like a charm! Thanks!!! Btw, can you please provide a short explanation for the WHERE DATEDIFF ? It seems SFMC SQL syntax is a little different when it comes to the convert function? – nix9247 Oct 3 '19 at 12:29
  • I've updated my answer to explain what I did – zuzannamj Oct 3 '19 at 12:50
  • Ok, just in the CONVERT section, why do we need to add Date_Dispo + ' 12:00:00 AM' and 105 code (Italian) first, then to be converted again to US (101)? – nix9247 Oct 3 '19 at 12:52
  • Ok thanks for clarifying. – nix9247 Oct 3 '19 at 14:24

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.