Date is showing a different date than the Date/Time Field

Question

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 created

Formula Field created

Example of how the date transfers 1 day ahead.

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

Answer 1

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))

Answer 2

Why so complicated solution?

Here's one I posted on the Dev community board a couple months ago:

Having had the same issue this morning , i found a simple solution to use NOW() function and get proper time according to time zone

I'm EST and there is a +4h difference with GMT

Using formula NOW() -4 would take out 4 days and not 4 hours , so what i did is:

1 day/24h = 0.0416666666 (value of 1 hour)

0.041666666(value of 1 hour) x 4 (how many hours difference my timezone to GMT) = 0.166666664

then I used (NOW () - 0.166666664) and that gave me current time

hope it helps