What is the best way to convert a UTC string to a date in Crystal Reports?

Question

Given a UTC time string like this:

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

What is the best way to convert it to a DateTime using a Crystal Reports formula?

My best solution is posted below.

I hope someone out there can blow me away with a one-liner...

Answer 1

Here is my best solution, with comments:

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

Answer 2

Here you go:

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

Answer 3

May I suggest a simplification to jons911's answer:

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

This has the advantage that the formula works for time zones forward of UTC 0 (e.g. +01:00). It does of course rely on the UTC string being a properly formatted ISO 8601 string.

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 . \n'\r'\nA string is required here."