Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Shannon Andreas 21Shannon Andreas 21 

Date/Time Field Converted to Time Field Considering Daylight Savings Time

Hello Friends!

I need your help! I have scoured through all of the posts regarding Date/Time conversion to Time plus Daylight Savings Time/Standard Time. I can't find a formula that fits my situation. These are the fields:

Start Date/Time: 08/16/2017 3:00 PM 
New Custom Field: 3:00 PM (would this be Date/Time formula field or text field?)

The formula will have to consider DST, in this case, Central Daylight Time as well as Central Standard Time.

The difference between GMT and CDT is -5
The difference between GMT and CST is -6

I would prefer a formula field if I could, but if someone has apex code and that is the only solution, then I would be happy with that as well.

Thanks in advance for your help,

Shannon
Shannon Andreas 21Shannon Andreas 21
bump
Shannon Andreas 21Shannon Andreas 21
Can someone please help me?
Alain CabonAlain Cabon
Hi,

Just extracting the time (Hour/Minutes) and AM/PM from a DateTime field is very complex with a formula (always a bit crazy to do that from the text function  TEXT( date/time - TZoffset )):  https://success.salesforce.com/answers?id=9063A000000iU8pQAE

For me, it is a very theoritical need. The date type "TIME ONLY" doesn't exist for a field in Salesforce (it is always date/time).

The most important is the final use of these times  (What's the point of having just the times without the complete dates/times? VFP fields? reports? export of data?)

It is easy to format a date keeping only the times directly in a VFP for example where the time zones are managed "automatically" according the user time zones.

In Apex, it is also possible to format a date/time and with a trigger to populate a field text but it could be useless if the final need is just a VFP field shown on a screen.

Apex: format(dateFormatString, timezone) : Converts the date to the specified time zone and returns the converted date as a string using the supplied Java simple date format. If the supplied time zone is not in the correct format, GMT is used.
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_datetime.htm

The result will be a string that could be stored in a text field for just the time part because it is a controlled forced target timezone that is needed.

The time zone values you can use in Apex are any valid time zone values that the Java TimeZone class supports.

JAVA: Three-letter time zone IDs: For compatibility with JDK 1.1.x, some other three-letter time zone IDs (such as "PST", "CTT", "AST") are also supported. However, their use is deprecated because the same abbreviation is often used for multiple time zones (for example, "CST" could be U.S. "Central Standard Time" and "China Standard Time"), and the Java platform can then only recognize one of them.

Regards
Shannon Andreas 21Shannon Andreas 21
So the reason for doing this is email template! I need to list the Times an event is going to take place. If you have another suggestion, that would be great! Basically, the template would have the following layout:

Date of Event: 08/11/2017
Time of Event: 8:00 AM - 9:00 AM
Event#: Order-123456

They currently have a field that is Date/Time that would show the date and time of the event. I just need to break it out for the email.

Let me know if you have some other idea! I am listening!

Thanks,

Shannon
Alain CabonAlain Cabon
Hi Shannon, 

You should have started by that (used in an email template).

Now we can start to create a visualforce email template but you are very demanding with your CDT/CST timezones that java is not willing to hear any more about.

There is a draft of my idea here: 
https://salesforce.stackexchange.com/questions/61579/visualforce-page-display-time-in-timezone

I will come back when my solution will work and other people could have already an email template ready to use here with the very common start/end dates of events.

Best regards
Alain 
Shannon Andreas 21Shannon Andreas 21
Hey Alain,

The stuff out there is great, if I didn't have a moving target with DST!

Okay, so I am trying to create an email that is sent to client's for an upcoming event. I need to list:

Date:
Time: Start-End
Event#

The Start time is already captured in a field called "Delivery Window From". The End time is already captured in a field called "Delivery Window To". I am trying to break out the date and time so I can plug them into the email template (in the format you see above). 

I need to be able to extract the time from those fields, then convert from GMT to CST or CDT.

The time zone is recorded correctly in these fields. When I try to use a formula, it forces me to enter a TZOffset which could be -5 or -6 depending on DST. I need a way to account for either of those scenarios in whatever formula, VF page, Apex code I use.
Alain CabonAlain Cabon
Hi Shannon,

Here is a more complete draft test for a visualforce email template which can be used as a pattern for your need (perhaps):

This email template uses a visualforce component: ComponentDateEmail​ 
<messaging:emailTemplate subject="New Scheduled Event" recipientType="User" relatedToType="Event">
<messaging:htmlEmailBody >
<html>
<body>
    <p>Test of a very interesting message indeed (for you only, my Lord)</p>
    <c:ComponentDateEmail theEvent="{!relatedTo.Id}"/>
    <br/><br/>
</body>
</html>
</messaging:htmlEmailBody>
</messaging:emailTemplate>

User-added image

User-added image


Visualforce Component: ComponentDateEmail​ (uses the controller: ControllerDateEmail ) 
<apex:component controller="ControllerDateEmail" access="global">
    <apex:attribute name="theEvent" type="Id" description="the event id" assignTo="{!relatedEventId}" />
    
    <style type="text/css">
        .FS-table  {border-collapse:collapse;border-spacing:0;}
        .FS-table td{font-family:Arial;text-align:center;sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;word-break:normal;}
    </style>
    <h1><strong>Hello Master of the Puppets!</strong><i>(... or any boss'name if you prefer, this is just a test)</i></h1>
    <table class="FS-table">
          <tr>
            <td><b>Subject</b></td>
            <td colspan="2">{!myDateStartEnd.mySubject}</td>
        </tr> 
          <tr>
            <td><b>Date</b></td>
            <td colspan="2">{!myDateStartEnd.myStartDate}</td>
        </tr> 
        <tr>
            <td><b>Hours</b></td>
            <td>{!myDateStartEnd.myStartDateTime}</td>
            <td>{!myDateStartEnd.myEndDateTime}</td>
        </tr> 
    </table>
     <h2><strong>That's all ...</strong></h2>
</apex:component>

Apex controller (the whole power is here):  I use the Time Zone Sid Key of the owner of the event. 
public class ControllerDateEmail {
    
    public String relatedEventId {get;set;}
    
    public DateStartEnd myDateStartEnd {get{return (relatedEventId==null?new DateStartEnd():new DateStartEnd(relatedEventId)); } set;}
    
    public ControllerDateEmail() {          
    }
    
    public class DateStartEnd {
        public String myStartDate {get;set;}
        public String myStartDateTime {get;set;}
        public String myEndDateTime {get;set;}
        public String mySubject {get;set;}
        public DateStartEnd() {
            myStartDateTime = null;
            myEndDateTime = null;
        }
        public DateStartEnd(String relatedEventId) {
            String query = 'SELECT Id, OwnerId,subject, WhatId,StartDateTime,EndDateTime from Event WHERE Id = \''+relatedEventId+'\' LIMIT 1';       
            Event myEvent = Database.query(query);
            system.debug('myEvent: ' + myEvent);
            String ownerid = myEvent.ownerid;
            if (ownerid == null) return;
            User  myUser = [select id,TimeZoneSidKey from user where id = :ownerid ];
            myStartDate =  (myEvent.StartDateTime==null?'':myEvent.StartDateTime.format('MM/dd/yyyy', myUser.TimeZoneSidKey));
            myStartDateTime =  (myEvent.StartDateTime==null?'':myEvent.StartDateTime.format('HH:mm a z', myUser.TimeZoneSidKey));
            myEndDateTime =  (myEvent.StartDateTime==null?'':myEvent.EndDateTime.format('HH:mm a z', myUser.TimeZoneSidKey));
            mySubject = myEvent.Subject;
        }
    }
}

Best regards
Alain