0

I have an ORDER_DATE attribute in my data extension which is of the text datatype. The date is in the following format mm-dd-yyyy.

Now I want to convert this date into European format based on locale but I am getting some error while converting it.

I am using the following piece of code:

%%[

  var @orderDate 
  set @orderDate = ORDER_DATE
  set @convertdate = FormatDate(@orderDate,"l","","en-GB") 
  Output(Concat("OrderDate: ", @convertdate))

]%%

Why the date is not showing me when I do preview and test??

  • I would verify your date values. I tested your code and it works correctly. Perhaps there are some that are either empty or malformed that are causing errors? As @JohannesSchapdick stated, it could be the special char. in your field name (developer.salesforce.com/docs/…) that is causing the mismatch as well. I don't think I have had issues with underscores before, but it is possible. Try outputting just your @orderDate and verify the results. – Gortonington Nov 4 '19 at 14:26
  • Please update your question with a sample of text dates from your sending Data Extension. – Adam Spriggs Nov 4 '19 at 14:59
1

Depending on how your text dates are formatted, you many need to do some string manipulation before applying the formatting:

%%[

  var @orderDate 
  var @convertDate

  set @orderDate = AttributeValue("ORDER_DATE")
  output(concat("<br>orderDate: ", @orderDate))  

  set @convertdate = FormatDate(replace(@orderDate," ",""),"l","","en-GB") 
  output(concat("<br>convertdate: ", @convertdate))

]%%
0
set @orderDate = ORDER_DATE

This code will not actually set a value into the variable orderDate. Is it a dataextension field? Probably yes -> thats why you would need to set the variable like this

set @orderDate = [ORDER_DATE]

Here is additional information about the formatdate function:

- Refence Salesforce

- Reference AMPScript Guide

  • I don't believe there is any functional difference between your two lines of AMPscript. As far as I know, brackets aren't required unless there's a space in the variable name. – Adam Spriggs Nov 4 '19 at 15:01
  • It is also needed for some special characters (e.g. -) but I honestly don't think that underscore is one that would require it. (ref: developer.salesforce.com/docs/…) – Gortonington Nov 4 '19 at 21:00
  • 1
    Oh, yeah, dash. Forgot about that one. – Adam Spriggs Nov 5 '19 at 3:49
0

Actually the thing is FormatDate() function takes input as datestring in the format YYYY-MM-DD.And the date format in my DataExtension was DD-MM-YYYY. So I have used following code for converting it in to YYYY-MM-DD and then based on locale it is now converting in to European Format.

Following is the code-

     %%[ var @orderDate 
        set @orderDate = AttributeValue("ORDER_DATE")
        if indexOf(@orderDate, "-") > 0 then
         set @dd = Substring(@orderDate,1, Subtract(IndexOf(@orderDate,"-"),1))
       endif 
        if indexOf(@orderDate, "-") > 0 then
         set @MM = Substring(@orderDate,4, Subtract(IndexOf(@orderDate,"-"),1))
       endif
       Set @YYYY = Substring(@orderDate, SUBTRACT(Length(@orderDate), 3), 4) 
        set @dateformat=Concat(@YYYY,"-",@MM,"-", @dd)
         set @convertdate = FormatDate(@dateformat,"l","","en-GB") 
        output(concat("<br>convertdate: ", @convertdate))]%%

Please suggest if there is any better solution than this.!!!!!!

0

Going off your answer that your dates are in the DD-MM-YYYY format, I would propose using a SQL query beforehand to correct this.

I recommend SQL as Format and FormatDate do not recognize or correctly handle 'day before month' date formats. They can output them, but they are not a recognized input.

Now the down and dirty SQL I would recommend using is: CONVERT(DATETIME,CONVERT(DATETIME, ORDER_DATE,103),101) as ORDER_DATE

Basically what this does is take your varchar text date (Current ORDER_DATE) and convert it to a date data type (103 - which is DD-MM-YYYY). From there it then does another convert to turn the 'Day First Date' to a 'Month First Date' which is easier for most date related functions to accept as an input.

I would recommend adding a second date field here in the DE if possible. Something like ORDER_DATE_FORMAT and have it be nullable and be an actual date type. This will then also keep your original ORDER_DATE info in the DE untocuhed.

So for instance, after you run the SQL, in your email you would have:

%%[
  set @orderDate = AttributeValue("ORDER_DATE_FORMAT") 

  set @convertdate = FormatDate(replace(@orderDate," ",""),"l","","en-GB")

]%%

inside the email to then turn that date into the 'long' description date.

I would look at finding a way to optimize the query for converting the date - e.g. only run it on new records (e.g. those that do not have a value in ORDER_DATE_FORMAT or similar.)

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.