[SalesForce] Date is showing a different date than the Date/Time Field

I have an issue when entering a date/time into a date field where the date increases by 1 day when after a certain time. Example would be 1/1/13 5:23PM PST converting to 1/2/13. This also occurs when I change my time zone setting. I have traced it back to being that GMT would have the date set forward to the next date. But I am not certain why it would not show the proper date even if it was converted.

This also happens when I am passing a date/time from a third party tool which is also in GMT. When it populates into a date/time it shows the correct date but when i push to a date field it is converted forward 1 day.

I have test on both standard and custom objects and the behavior is the same.

My test consisted of creating a Date/Time field and a Formula field with a date data type. I would insert the Date/Time field into the Formula field.

2 fields created2 created fields

Formula Field created

Formula Description

Example of how the date transfers 1 day ahead.

Example of wrong date

Example of how the date is correct if Time stamp is not > 5PM PST.

Example of wrong date 2

Best Answer

The datetime in salesforce is always stored in GMT (UTC). The time is corrected to the users time zone when displaying it on the UI. The time is only corrected in the UI, so you need to make the adjustment yourself for formula fields.

Datetime input field cast to another timezone

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm

You need to adjust for your own time zone in your formula to account for this. In my formula fields, since I am in EST, I have to adjust all the datetimes by 5 hours to make sure I have the correct values. I just do this in a long case statement like below. this formula finds the hour of the day that a case was created, notice I have to adjust each time by 5 hours to make it work

TEXT(CASE(VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)), 
00,19, 
01,20, 
02,21, 
03,22, 
04,23, 
05,00, 
06,01, 
07,02, 
08,03, 
09,04, 
10,05, 
11,06, 
12,07, 
13,08, 
14,09, 
15,10, 
16,11, 
17,12, 
18,13, 
19,14, 
20,15, 
21,16, 
22,17, 
23,18, 
00))
Related Topic