如何在Crystal Reports中将UTC字符串转换为日期？

Question

对于像这样的UTC时间字符串：

2005-11-01T00:00:00-04:00

如何使用Crystal Reports公式将其转换为DateTime的最佳方法是什么？

我提供的最佳解决方案如下。

希望有人能用一行代码让我惊艳...

- kogus

---

坚持你的解决方法。一个“一行代码”的方法只会使代码变得难以阅读。理论上，你可能会获得几个时钟周期，但所得到的好处微乎其微，不值一提。 - Jim C

Answer 1

我可以向jons911的回答提出简化建议：

CDateTime(CDate(Left({@UTCString}, 10)), CTime(Mid({@UTCString}, 12, 8)));  

这样做的好处是，该公式适用于UTC 0时区之后的时区（例如+01:00）。当然，这需要UTC字符串是格式正确的ISO 8601字符串。

- Anthony K

Answer 2

请看下面的内容：

CDateTime(CDate(Split({?UTCDateString}, "T")[1]) , CTime(Split(Split({?UTCDateString}, "T")[2], "-")[1]))

- jons911

Answer 3

这是我的最佳解决方案，带有注释：

//assume a date stored as a string in this format:
//2005-11-01T00:00:00-04:00
//return a DateTime value
StringVar fieldValue;
StringVar datePortion;
StringVar timePortion;
NumberVar yearPortion;
NumberVar monthPortion;
NumberVar dayPortion;
NumberVar hourPortion;
NumberVar minutePortion;
NumberVar secondPortion;

//store the field locally so i can easily copy-paste into another formula
//(where the field name will be different)
//Crystal formulas do not use a powerful language.
fieldValue := {PACT.ReferralDate};

//break up the date & time parts.
//ignore the -04:00 offset part of the time.
datePortion := Split (fieldValue,"T")[1];
timePortion := Split(Split (fieldValue,"T")[2],"-")[1];

yearPortion := ToNumber(Split(datePortion,"-")[1]);
monthPortion := ToNumber(Split(datePortion,"-")[2]);
dayPortion := ToNumber(Split(datePortion,"-")[3]);

hourPortion := ToNumber(Split(timePortion,":")[1]);
minutePortion := ToNumber(Split(timePortion,":")[2]);
secondPortion := ToNumber(Split(timePortion,":")[3]);

//finally, return the result as a date-time
DateTime(yearPortion,monthPortion,dayPortion,hourPortion,minutePortion,secondPortion);

- kogus

Answer 4

StringVar fieldValue;
StringVar datePortion;
StringVar timePortion;
NumberVar yearPortion;
NumberVar monthPortion;
NumberVar dayPortion;
NumberVar hourPortion;
NumberVar minutePortion;
NumberVar secondPortion;
datetimevar dtlimite;

fieldValue := {dtsecretaria.Data_limite};

datePortion := Split (fieldValue,"T")[1];
timePortion := Split(Split (fieldValue,"T")[2],"-")[1];

yearPortion := ToNumber(Split(datePortion,"-")[1]);
monthPortion := ToNumber(Split(datePortion,"-")[2]);
dayPortion := ToNumber(Split(datePortion,"-")[3]);

hourPortion := ToNumber(Split(timePortion,":")[1]);
minutePortion := ToNumber(Split(timePortion,":")[2]);
secondPortion := ToNumber(Split(timePortion,":")[3]);

dtlimite := DateTime(yearPortion,monthPortion,dayPortion,hourPortion,minutePortion,secondPortion);


if dtlimite > CurrentDateTime then

Color(255,0,0)

else

Color(255,255,0)



error of nError in formula  <Back_Color>. \n'\r'\nA string is required here."

- alfinete