0

We need a specific format date to personalize our emails, for example, the US date format is MM/DD/YYYY HH:MM:SS and we need to display DD/MM/YYYY.

Is it possible to change the date format directly in the DE through the Query so that we can use personalization strings (and no ampscript)?

We tried in the query, in the Select part something like format(Date_field,'dd/MM/yyy') as Datefincarte, and we had a "system error".

4

Date data types in SFMC are DateTime (or DateTime2) data types behind the scenes, so the time is always included. It's best to use AMPscript to format these, as you can't dictate the format stored in the data extension unless you store them as Text.

I generally just use the formatDate() function in AMPscript:

%%[

 var @date_field
 set @date_field = AttributeValue("date_field")
 set @date_field = formatDate(@date_field, "s") /* s = short date*/

]%%
<br>The date is %%=v(@date_field)=%%

Depending on the timezone offset of your DateTime value, you may need to use the LocalDateToSystemDate function to align the time to your SFMC account's timezone.

%%[

 var @date_field
 set @date_field = AttributeValue("date_field")
 set @date_field = formatDate(LocalDateToSystemDate(@date_field), "iso")

]%%

If you want to store the date as a string in your query, you can create a new date_field_str column (or something like that) in your data extension and use a query like this to store it as Text:

select
convert(varchar (10), d.date_field, 1) as date_field_str
from YOURDATAEXTENSIONNAMEHERE d

The 1 argument on the convert function specifies the format. Formats are listed on this page.

Reference

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.