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))
The documentation on the data extension data types reads:
Date
- A system date
- You can use an AMPscript function to format this value when you present it in a message
- Attributes of a date type will accept dates in various formats, such as 1/1/2005 or January 1, 2005
- All dates are stored in the format MM/DD/YYYY, and if a date is entered in a different format, the system will convert it to the
proper format when storing it
- Valid date values begin after 12/31/1772 and before 01/01/10000
- You can add a HH:MM time value following the date value, such as 12/12/2012 12:12 PM.
As long as your data extension uses the data format for the column, and your string is in the proper format according to the above guidelines, you should be ok.
It appears that we do not, however, store as a 24-hour time (which btw is not GMT per se, unless you are not using a timezone offset). A simple fix for this would be to check for AM/PM and add 12 when parsing from the DE, and check for >12 and modify when going back to the DE to store.
To use Concat() to pull individual items together, the string would have to match the above valid date format.
CONCAT(@month,"/",@day,"/",@year," ",@hour,":",@minute," ",@ampm)
This will produce a valid string, especially when using DatePart(), since it returns a string of the segment you requested.
Best Answer
It may be the "SystemDateToLocalDate", as that converts it to the user's time. For example, when I used that "SystemDateToLocalDate" 9AM in the Salesforce field converted to 11AM in the test email. This doesn't happen when I do not include "SystemDateToLocalDate". Below are some screenshots. Hope this helps!