SQL Server 2008 (ET uses SQL Server) has a date type with no time, to which you could convert. Use something like
SELECT CONVERT(date, getdate()) AS testDate
for your query
----UPDATE----
Based on the additional query added, I would do
SELECT CONVERT(date, O.EVENTDATE) AS someDate FROM _Open O
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
Best Answer
I'm assuming you are referring to the Query Activities within the ET application. They sit on top of a SQL Server backend, with 90% of the SQL Server functionality exposed to the end user. So yes, things like CONVERT still work.
The only way the formatting actually matters is if you are storing the results in a field of type string. Otherwise, it will get stored as a SQL dateTime, and you will need to format accordingly when you pull the value out.
Hope this helps!