Date/Time Format Change

[saisowmya]

Hi,

I want to change the 'Opened' field format on Problem Table. Currently it is in "yyyy-MM-dd hh:mm:ss" format and now I want to change this to "dd-MM-yyyy hh:mm:ss AM/PM". As I want to change this to only one field I cannot change the Global System Properties. Plz suggest.

Thanks,

Sowmya

[Geoffrey2]

Hi Sowmya,

You're probably going to have a lot of trouble trying to do this.   Date fields are displayed in the system format by default, but can be overridden by the User's preferred format. So the value isn't always going to be the system default of "yyyy-MM-dd hh:mm:ss". If you go to your User record and change your Date format to something else then go back to the Problem form you will see the date formatted according to what you selected on your User profile.

On top of the different formats, the date and time is also displayed in the current User's timezone. So the display value changes too.

You can of course write code to read any of these formats and reformat them (I just provided an example here Unable to Select the Current date ), but then if you change the value of the date field to something other than the expected User format, then you will likely have troubles saving the form.

One alternative you can try is you could create a custom string field, like u_opened_at, then write the date in any format you like in that field. Since it is a standard string field, it will not be changed by the system or User's preferred date format. Take the original field off the form and replace it with the new one. Of course if the field is editable, there will be no date picker.   But the problem with this is that it will not adjust to the User's timezone. So it might only be suitable if all your users are in the same timezone. It's more complex then just reformatting a value.

[saisowmya]

Thanks for the reply. Can you please suggest how to write the date in required format in the custom field

[Geoffrey2]

Create a before update Business Rule

Condition: current.opened_at.changes()

Script:

(function executeRule(current, previous /*null when async*/) {

       if (current.opened_at.nil()) {
               current.u_opened_at = '';
               return;
       }

       var gdt = new GlideDateTime(current.opened_at);
       gdt.addSeconds(36000); // System timezone is +10, 10 hours is 36,000 seconds

       var dateTime = String(gdt.getValue());
       var date = dateTime.substring(0,10);
       var time = dateTime.substring(11);
       var hour = parseInt(time.substring(0,2), 10);
       var period = 'AM';

       if (hour > 12) {
               hour -= 12;
               period = 'PM';
       }

       if (hour < 10)
               hour   = '0' + hour;

       var newFormat = date + ' ' + hour + time.substring(2) + ' ' + period + ' (AEST)';

       current.u_opened_at = newFormat;

})(current, previous);

EDIT: I added the adjustment for the system timezone.