Reformatting a non mandatory date field in expression editor | Community
Skip to main content
C
Cogs
Level 2
September 7, 2021
Solved

Reformatting a non mandatory date field in expression editor

  • September 7, 2021
  • 2 replies
  • 2203 views

I have a requirement to output date in the format DD/MM/YYYY. However, our Adobe Campaign Standard has a default date format of YYYY/MM/DD. 
I do not have system admin access and have no idea if we can change this default format. That would be good to know as a possible solution but for now I need to reformat from YYYY/MM/DD to DD/MM/YYYY. The excellent solutions such as the ones below only work for a field that is populated all the time. I need something different for a date field that is sometimes blank.
Solved: Reformatting date field in expression editor - Adobe Experience League Community - 413026

Solved: Re: Convert System Date to mmddyyyy format - Adobe Experience League Community - 401612
In detail expressions such as:

Iif(Day([your_date_field]) > 9, ToString(Day([your_date_field])), '0' + ToString(Day([your_date_field]))) + '/' + Iif(Month([your_date_field]) > 9, ToString(Month([your_date_field])), '0' + ToString(Month([your_date_field]))) + '/' + ToString(Year([your_date_field]))

return "0/0/" when the optional date field is blank. How do we avoid that and return a blank output for a blank input?
The only thing I get possibly working is wrapping all of that in an Iif and checking if the result is '0/0/' then output '' else output the expression. There must be a more efficient / elegant way? Something using a function to check if a date field is blank?
I have tried trying IsEmptyString function and IS NULL (SQL) but Campaign complains they need a CAST (but adding a ToString does not work).
Many thanks in advance to any suggestions

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Cogs

I did get to a solution for this (or at least a part solution). Somewhere the default date was set to YYYY/MM/DD but I could not get it turned to DD/MM/YYYY format as a default. However - for Direct Mailings (DM) at least - I could get the format I wanted - for dates fields that are only sometimes populated. It does not need the expression editor for DM.

The solution:

edit the Direct Mail Delivery activity, Click on the Content area to edit that, click on the "File Structure" tab, Go down to the Date Format section, click to expand the heading and then -for that specific mailing - you can change the Date format and the separator. There I changed the format to ddmmyyyy and the separator to "/"

Note - I only need this for Direct Mailings which the above fixes so have not investigated if this is also possible for other communication types like email, etc.

2 replies

Jyoti_Yadav
Jyoti_Yadav
Level 8
September 13, 2021

Hi @cogs ,

Try using formatDate method if it works,
<%= formatDate(targetData.Date1, "%2D/%2M/%4Y") %>

or

Year(GetDate())+''+ Iif(Month(GetDate())>9, Month(GetDate()), '0'+Month(GetDate())) +''+Day(GetDate())

or 

Substring(ToString(GetDate()),1, 4)+''+Substring(ToString(GetDate()),5, 2)+''+Substring(ToString(GetDate()),7, 2)

the number in red is ajustable regarding what your ToString(GetDate()) return

 

Thanks,

Jyoti

 

 

C
CogsAuthor
Level 2
September 13, 2021

Thanks for the reply Jyoti

The two suggestions you give under the OR parts of the reply will not work for this specific query. They only work where the data is always populated like GetDate() - they do NOT work for my specific query where the date is only populated some of the time. They are lifted from other posts and do not include the date separator I need.
As for the "<%= formatDate(targetData.Date1, "%2D/%2M/%4Y") %>" option I have not tried this. I do not think it is part of what would work in the expression editor - it is certainly not on the list of functions here: List of functions | Adobe Campaign. I did however get a solution from others at work which I will post as a reply to my first comment above.

C
CogsAuthorAccepted solution
Level 2
September 13, 2021

I did get to a solution for this (or at least a part solution). Somewhere the default date was set to YYYY/MM/DD but I could not get it turned to DD/MM/YYYY format as a default. However - for Direct Mailings (DM) at least - I could get the format I wanted - for dates fields that are only sometimes populated. It does not need the expression editor for DM.

The solution:

edit the Direct Mail Delivery activity, Click on the Content area to edit that, click on the "File Structure" tab, Go down to the Date Format section, click to expand the heading and then -for that specific mailing - you can change the Date format and the separator. There I changed the format to ddmmyyyy and the separator to "/"

Note - I only need this for Direct Mailings which the above fixes so have not investigated if this is also possible for other communication types like email, etc.

Terms & ConditionsAccessibility statement

Loading footer...