EnterpriseWizard.com

If you are familiar with the common method of storing dates, you know that dates are not usually saved in a database in a form like “Dec 12, 2009 14:26:00” but as the number of milliseconds since Jan 1 1970 00:00:00.
The basic date/time fields don’t give you many options about how this data is displayed, but there are other things that can be used.
Within the team setup, the admin can set different options about the formatting for dates and times. These are mostly based around international vs US presentation of numbers and dates.

For reporting or presentation purposes, by using other data types we can expand our options further. Through a combination of default values/rules and formulas, we can populate text fields to either account for different user time zones or to display days of the week.
One of the tools we make use of here is the dateformat formula, a common java object. A full write-up of the dateformat formula is below the examples.
Example 1: On the Contacts table add a field called Time Zone Offset. This can either be an integer field or an elapsed time field. Add it to the layout and populate it for your admin login.
Then go to Support Cases or another table and create a short text field called Date Created – Local. Give it a default value based on the formula – dateformat(“MMM dd yyyy hh:mm:ss”, $date_created + $global.my_time_zone_offset). If your Time Zone Offset field is on integer type, then you need to add the word ‘hours’ before the closing parenthesis. Set this default to update whenever the record is updated and to overwrite the previous value.
If you add this field to the layout and go edit a support case, you should see it automatically populate with the date created but corrected for your time zone.
By combining this with action buttons and a choice list, you could setup a converter to allow a user to choose a date field to convert into local time or push a local time they enter into another field.
Example 2: By setting up a background table to hold the date/time correlations and proper names, I was able to set up a conversion from the standard Gregorian calendar to the Islamic calendar. The data was imported from an Excel sheet populated with the calculated astronomical date/times, but also allowed an admin to make corrections if needed.
Using a similar technique from the first example and setting a linked field with a default value, I was able to display the date created as the corresponding date on the Islamic calendar.
This could also be done for any non-Gregorian calendar such as the Hebrew, Chinese, or Hindu calendars.
Again, by adding action buttons or extra fields, a user could be allowed to enter dates/times according to a different calendar and have those stored as Gregorian UTC time.
Example 3: If you want to pull other information from a date other than the specific time – such as the day of the week – or want to display a date/time in a format other than those selectable from the team setup, again we can use the dateformat function.
One customer wanted a report that showed support tickets not by date, but by day of the week. So they could see in a chart how many tickets came in on Monday, Tuesday, etc.
We did this by creating a short text field that is populated by a default value when a ticket is created. This default value used the formula – dateformat(“EEE”,TODAY()). We never had to show this field on the layout, but having it in the table allowed us to use it as the x-axis in the chart.
Dateformat function – this built in java object lets you re-express the data pulled from a date/time field. The technical write up can be found at http://java.sun.com/javase/6/docs/api/j ... ormat.html
This is available for use as a function in EnterpriseWizard. As a function this takes 2 operators: the desired format string, and the date/time to use.
The format string should be enclosed with double quotes, such as “EEE” or “K:mm a, z”.
The date/time being used will usually be referenced by an EnterpriseWizard variable, but could also include other time functions such as TODAY() or NOW().
The full form of a dateformat call would look like – dateformat(“EEE”,$date_created).
In addition to the standard date/time information, the dateformat function can also display the day of the week (as shown above), the era, am/pm, and the day of the year.
Specific Examples:
Day of the week – “E” – if you use the pattern “EEEE” then the full name will be returned (“Tuesday”), if you use fewer characters then the short form of the day will be shown (“Tue”)
Year – “y” – if you use “yy” you will get the abbreviated year (“09”), use “yyyy” for the full year (“2009”)
Month – “M” – if you use “MMMM” you will get the full name of the month (“August”), if you use “MMM” you will get the abbreviated form (“Aug”), if you use “MM” or “M” you will get the numerical value of the month (“08” or “8”)
Day in Month – “d”
Day in Year – “D”
Hour in day (0-23) – “H”
Hour in am/pm (1-12) – “h”
Minute – “m”
Second – “s”
Millisecond – “S”
Week in Month – “W”
Week in Year – “w”
For all of the uncommented examples above, the number of repetitions of the character give the minimum length of the returned number. Any smaller numbers will be padded with zeros to be long enough. (“DDD” -> “001” to “366”)