Converting date/time to string in Marketing Cloud's SQL - string to date conversion error

Question

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

Answer 1

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