Help with date format problem for noob

[Dan Curry]

I'm fairly new to XMPie - have been using for a few months and so far, so good. I have  many years of experience using other VDP applications, but I'm no scripter. I'm running into an issue with formatting a date. I can get it to work for one field, but not another.

I'm using uCreate Print Standard Version 12.01 build 14151 on InDesign CS203 (18.5.2) on a Mac running Big Sur 11.7.6. I know these versions are a little behind current versions but it's what I have at the moment.

The job I'm working uses data that has 2 different date fields in the CSV. One of the fields (let's call it Field1) the date in the data reads "Jul 31 2024 12:00AM" and I am using the following QLingo to format it so it reads "07/31/2024" and it works just fine:

FormatDate(|->[Field1], "MM/dd/yyyy")

The other field (Field2) has data that comes to me looking like this: "12/20/2023 0:00" and I want it to format the same way as the other date, so I use the same logic as the other one but it doesn't work - instead of returning "12/20/2023" it returns "01/01/1900" no matter what the values in the data are. The Qlingo for this one is"

FormatDate(|->[Field2], "MM/dd/yyyy")

Is there anything I can do to get this Field2 to format correctly? I've tried various combinations of using AsDate but I'm not getting anywhere. Luckily, the values in Field2 always end with "0:00" so I'm able to use Find and Replace:

(FindAndReplace(|->[Field2], "0:00", "") 

This works for this job, but I might not be so lucky on upcoming projects. Any guidance is appreciated!

[couch]

I just tested and this worked for me:

FormatDate(AsDate(|->[Field2]),"MM/dd/yyyy")

However, it could have to do with the data file format Unicode/UTF8/UTF16/etc.

If you want to be more robust in your logic for future databases, you could use something like this:

if (Length(|->[Field2]) > 6  AND Find(|->[Field2], " ",0))
{
   FormatDate(
      AsDate(
         SubString(
            |->[Field2],
            0,
            Find(|->[Field2], " ",0)
          )
      )
   ,"MM/dd/yyyy")
}
else
{
   |->[Field2]
}

First, it checks that the Field2 is at least 6 characters long (considering a minimal date as "1/1/24" and has a space in it that you want to remove the time part.

It then finds the position of the space, and returns only the first part up until the space.

Parses that as a date and does the formatting.

[Dan Curry]

Thanks for testing that out. I was unable to get the first suggestion to work, even with changing the data file format to several different options.

The second more robust solution does work quite well - I may implement that in the future.

Thanks again!