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 when using the legacy propriety scripting language (BrainScript) within the Transform (Superseded) node. We recommend using the Transform node which supports Python. See this article for an example of how to use the Transform node and the Transform node section of the online documentation for further details.
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.
Useful functions:
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.
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:
myMonth_mmm =
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"
else "Dec"
4) Finally, I can piece all the components together in my desired order DD-MMM-YYYY
myNewDate = myDay + "-" + myMonth_mmm + "-" + myYear
Notes:
- An example data flow with the above functions are attached to this article.
- 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.
- 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: Advanced topics > Creating library nodes
- You can take the same approach with time data types as well, using functions like hour(), minutes() and seconds().
Comments
1 comment
This was very helpful in reformatting some dates in one my datasets. Thank you!
Please sign in to leave a comment.