This article provides an example of how you can format a field with date data type to a string field with a different format. THIS ARTICLE IS ONLY RELEVANT IF YOU ARE USING BRAINSCRIPT. In Dataverse we recommend using the new Transform node which supports Python. This article provides an example of using Python in the new Transform node to generate string representations of date objects.
You will notice that when you output a field that has been formatted as a date data type, the output takes the format YYYY-MM-DD. There are times you may want or must change the format of the output e.g. you want your report to show the date as MMM-YYYY or because a downstream application expects a specific format, such as DD-MMM-YYYY.
If you already have a field formatted as a date, you can use the day(), month() and year() functions to help you extract the various parts of the date and piece it back together into the format of your choosing.
You can see the help for these functions here: Dataverse Help > Dataverse Script help > Date and time operators
Example: Format my date field 'myDate' into a string with format DD-MMM-YYYY i.e. "2017-11-01" becomes "01-Nov-2017"
1) First, identify the day, month, and year components from my date field
I can use the following to extract the day, month and year:
myDay = 'myDate'.day() # Extract day from date i.e. 1
myMonth = 'myDate'.month() # Extract month from date i.e. 11
myYear = 'myDate'.year() # Extract year from date i.e. 2017
If I emit these new variables, my data will look this the following:
2) Then pad the day value to ensure it is always two characters long
Note these functions return an integer value but I want to ensure that my day value is always two characters long i.e. "01" instead of "1" for January.
I can use the pad() function on myDay field to add leading zeros - the pad() function can be used on string values so I will first need to use the str() function to convert the myDay field to a string data type:
myDay = myDay.str().pad(2)
Now my data looks like this:
3) Convert the month integer to a three-character string i.e. "Jan" for January
I can use conditional logic to perform this conversion:
if myMonth == 1 then "Jan"
else if myMonth == 2 then "Feb"
else if myMonth == 3 then "Mar"
else if myMonth == 4 then "Apr"
else if myMonth == 5 then "May"
else if myMonth == 6 then "Jun"
else if myMonth == 7 then "Jul"
else if myMonth == 8 then "Aug"
else if myMonth == 9 then "Sep"
else if myMonth == 10 then "Oct"
else if myMonth == 11 then "Nov"
4) Finally, I can piece all the components together in my desired order DD-MMM-YYYY
myNewDate = myDay + "-" + myMonth_mmm + "-" + myYear
- An example data flow with the above functions are attached to this article. More information on how to import the data flow into Dataverse can be found here: Sharing data flows and library nodes
- If your date field isn't already formatted as a date, you can take a similar approach as above but using the left(), right() and substr() functions to extract the relevant characters from your string value. More information on these functions can be found in the help: Dataverse Help > Dataverse Script help > String operators
- If you find that you often convert the integer results to a string value (like in step 3) or you simply want an easier way to share your great logic with your team members, then it is a great idea to save the logic as your own custom library node. For more information: Dataverse Help > Advanced topics > Creating library nodes
- You can take the same approach with time data types as well, using functions like hour(), minutes() and seconds(). More information on date and time operators can be found here: Dataverse Help > Dataverse Script help > Date and time operators