Monday, May 19, 2008

Using Calculated Fields to Parse Dates

We had a requirement to calculate the name of Campaign based on a few attributes. This was accomplished by using a post default of the calculated field into the stored Name field. Additionally, if any of the attributes changed, we used the On Field Update bc user prop to recalculate the Name. Pretty straightforward except that one of the fields was the Created date field that had to get parsed from the MM/DD/YYYY TIME format to something like YYYY-MM-DD. Here's how it was done:

1. Create 3 new calculated fields to grab the MM, DD, YYYY out of the date
a. Year Calc: JulianYear([Created])-4713
b. Month Calc: JulianMonth([Created])-(JulianYear([Created])*12)
c. Day Calc: Right(Left([Created],5),2)

2. String them together as:
ToChar([CEB Year Calc])+"-"+ToChar([CEB Month Calc])+"-"+(Right(Left([Created],5),2))

Figuring out the Day Calc was not straightforward w/ out-of-the-box (oob) Julian function, so I had to parse it the old fashioned way. In reality, I probably could have parsed the three date elements using Right and Left but there was a nice challenge in using Julians. I had to use some simple algebra to solve for Month since bookshelf presents it in terms of Julian Year. I also like the Julian function b/c it should handle any regional differences in date formats.

No comments: