Unparseable date

Hello,

I have been trying to export a dataset to CSV. The export is working but I am also trying to format the date/time. Currently it is being exported in the form “Tue Sep 13 02:16:56 PDT 2022” but I need to be in this form “2022-09-13 02:16:56.107”. I have tried using

system.date.parse(myDataset.getValueAt(row,column), "yyyy-MM-dd HH:mm:ss.SSS")

As well as

system.dataset.formatDates(myDataset, "yyyy-MM-dd HH:mm:ss.SSS")

But I haven’t been able to get either to work for me. When I use system.date.parse, I get

java.text.ParseException: java.text.ParseException: Unparseable date: "Tue Sep 13 02:16:56 PDT 2022"

And when I use system.dataset.formatDates, the execution seems to skip over the line of code because the dates have not changed in the export.

Can someone please let me know what I am doing wrong?

Thank you in advance.

Can you start by printing the type of the value in the dataset?

print type(myDataset.getValueAt(row,column))

Whether it is a text representation of a date or a Date object will determine what the next steps in the transformation are.

Sounds like you aren’t assigning the return value from system.dataset.formatDates. Datasets are immutable–unchangeable. Functions like this one return a new dataset with the requested changes. Assign that to your table. (Or feed it to your CSV conversion.)

I have printed the type and it is ‘unicode’.

I am using system.dataset.formatDates like this:

myNewDataset = system.dataset.formatDates(myDataset, "yyyy-MM-dd HH:mm:ss.SSS")
filePathPD = system.dataset.exportCSV("processedData.csv", 1, myNewDataset)
1 Like

formatDates only works when the column type is actually Date, and yours appear to be String (unicode).

If you can’t get the Dataset to have a Date column in the first place, you’ll have to do this in 2 steps:

  1. parse the String representation you find in that column into an actual Date
  2. format that Date object back into the desired String format
1 Like

I have tried using system.date.parse(myDataset.getValueAt(row,column), 'MMMM dd, yyyy hh:mm') to create a Date object but I still get the error:
java.text.ParseException: java.text.ParseException: Unparseable date: "Tue Sep 13 02:16:56 PDT 2022" How can I get past this?

Probably by providing the right format to parse - you need to match the format the date is currently in.

1 Like

This worked for me, here is a link to the date formatting reference

dateStr = "Tue Sep 13 02:16:56 PDT 2022"
print system.date.parse(dateStr, "E MMM dd HH:mm:ss Z yyyy")
1 Like

This format seems to be the default, meaning you probably are making the dataset wrong somewhere earlier.

How is the dataset being filled?

Parsing the String representation and getting an actual Date was enough to make system.dataset.formatDates work