DateTime Conversion Crystal Reports 11

[nyangi]

I need help converting a string to a date time. My string in the format 20,121,109 for the date and 3,325 for the time. This should convert to 11/9/2012 with a time of 0:33:25. I'm using the formula below to convert it but when the time is between midnight and 1 my times are all messed up. Somebody please help.

Function (numberVar v1, numberVar v2)

//Date Conversion
local stringVar MyDate := CStr(v1);
local stringVar YearVar := MyDate[1 to 2] + MyDate[4 to 5];
local stringVar MonthVar := MyDate[6] + MyDate[8];
local stringVar DayVar := MyDate[9] + MyDate[10];

//Time Conversion
local numbervar MyTimeNum:= Int(v2);
Local stringvar MyTimeStr := cstr(MyTimeNum);
if MyTimeNum<100000 then MyTimeStr := "0"+MyTimeStr;

Replace(MyTimeStr,",","",1);

//fill in array
local stringVar TimeHold := MyTimeStr;
local stringVar Allzeros := "00000000";
Replace(TimeHold,".","",1);
local numberVar z := Length(Timehold);
//if z<8 then TimeHold := Mid(Allzeros,1,(8-z))+Timehold;

local stringVar HrsVar := mid(Timehold,1,2);
local stringVar MinVar := Mid(Timehold,z-8+1,1)+mid(Timehold,z-6+1,1);
local stringVar SecVar :=mid(Timehold,z-5+1,2);

//if isNumeric(MonthVar) and isNumeric(YearVar) and isNumeric(DayVar) and isNumeric(TimeString) then

DateTime(ToNumber(YearVar), ToNumber(MonthVar), ToNumber(DayVar),
ToNumber(HrsVar), ToNumber(MinVar), ToNumber(SecVar));

 

[CoSpringsGuy]

is it one string (20,121,109) and (3,325) if so .. the second looks like a number field






_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[nyangi]

No they are 2 different strings that I need to put together to create datetime.

 

[CoSpringsGuy]

looks suspiciously like number fields to me but if not then this crazy code should work.

datetime(
(tonumber(left(replace({datestring},",",""),4)))
,
(tonumber(mid(replace({datestring},",",""),5,2)))
,
(tonumber(mid(replace({datestring},",",""),7,2)))
,
tonumber(left(totext(tonumber({timestring}),"00000#"),2))
,
tonumber(mid(totext(tonumber({timestring}),"00000#"),3,2))
,
tonumber(right(totext(tonumber({timestring}),"00000#"),2))
)

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

stringvar strdt := {table.stringdate};
stringvar strt := {table.stringtime};
stringvar cdt := replace(strdt,",","");
stringvar ct := totext(val(replace(strt,",","")),"000000");
datetime(val(left(cdt,4)),val(mid(cdt,5,2)),val(right(cdt,2)),val(left(ct,2)),val(mid(ct,3,2)),val(right(ct,2)))

-LB

 

[nyangi]

I really do appreciate ya'll trying to help. I tried to use both codes but I got a type missmatch error.
"Type missmatch in argument. The function requires a String but inferred was a Number"
Thank again for your's help.

 

[CoSpringsGuy]

then your field is a number

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[nyangi]

Yes it is, my apologies.

 

[CoSpringsGuy]

in Crystal, on the right where you can see your fields .. right click one of the fields and select show field type.. let me know what it says for each field

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[nyangi]

It says number for both the date and the time fields.

 

[CoSpringsGuy]

see if this works

datetime(
int({datefield}/10000),
int(({datefield}-(int({datefield}/10000)*10000))/100),
int(({datefield}-(int({datefield}/100)*100))),
int({timefield}/10000),
int(remainder({timefield},10000)/100),
remainder({timefield},100)
)

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[nyangi]

I browsed the data in the date field and it's in the following format "20,050,105.00" and the time field is the format "819.00

 

[CoSpringsGuy]

see if this works

datetime(
int({datefield}/10000),
int(({datefield}-(int({datefield}/10000)*10000))/100),
int(({datefield}-(int({datefield}/100)*100))),
int({timefield}/10000),
int(remainder({timefield},10000)/100),
remainder({timefield},100)
)

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[nyangi]

This seems to have worked perfectly. Thank you so much. I really do appreciate your input.

 

[CoSpringsGuy]

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[nyangi]

Genius Solution!