Date / Time Conversion Question

[Former Member]

I have a string field length of 6 for both date and Time :

Field1 stores the date as 100808

Filed2 stores the time as 1000

I need to convert and print as acceptable dates. ie 10/08/2008 and 10:00am

Any help is appricated..

Jim

[former_member260594 · Accepted solution]

James,

Try the following formula;

stringvar d:= '100808'; //or database field

stringvar t"= '1000'; //or database field

numbervar y:= tonumber ( '20' & d[5 to 6]);

numbervar m:= tonumber ( d[1 to 2] );

numbervar dd:= tonumber ( d[3 to 4] );

numbervar h:= tonumber ( t[1 to 2] );

numbervar mm:= tonumber ( t[3 to 4 );

datetime( y, m, dd, h, mm, 0)

[Former Member · Accepted solution]

Sorry guys, with the following

;

stringvar d:=; //or database field stringvar t"= ; //or database field

numbervar y:= tonumber ( '20' & d5 to 6);

numbervar m:= tonumber ( d1 to 2 );

numbervar dd:= tonumber ( d3 to 4 );

numbervar h:= tonumber ( t1 to 2 );

numbervar mm:= tonumber ( t[3 to 4 );

datetime( y, m, dd, h, mm, 0)

it highlights the d5 in the 3rd Line and says

a number, currency amount, boolean, date, time, date-time, or string is expected here

[former_member260594 · Accepted solution]

James.

It appears the forum page took out the square brackets around the 5 to 6 and all of the other ranges. Just add teh square brackets adn you should be good to go.

so d left square bracket 5 to 6 right square bracket

Edited by: Graham Cunningham on Oct 8, 2008 8:31 AM

[Former Member · Accepted solution]

so close... now it points to the First Line, the stingvar D:= and says

a variable cannot be declared with a different type

[former_member260594 · Accepted solution]

Copy and paste the whole formula into a response and double check that the forum doesn't interpret anything differently

[Former Member · Accepted solution]

stringvar d:= {TAGDTL.USER_DEF_TEXT_1}; //or database field

stringvar t:= {TAGDTL.USER_DEF_TEXT_2}; //or database field

numbervar y:= tonumber ( '20' & d[5 to 6]);

numbervar m:= tonumber ( d[1 to 2] );

numbervar dd:= tonumber ( d[3to 4] );

numbervar h:= tonumber ( t[1 to 2] );

numbervar mm:= tonumber ( t[3 to 4] );

datetime( y, m, dd, h, mm, 0)

[former_member260594 · Accepted solution]

James,

It looks like there might be another formula that is using the variable name d. Try adding the scope Local to the stringvar d;

Local stringvar d:=.....

If this works then check other formulas to see if they have a variable named d

[Former Member · Accepted solution]

just change the name d to anything else like "ad" or "bd" as long as it is not the same as the other formula variable.

[Former Member]

Thanks for all your help.. The trim functions was part of the answer. I ended up paying for support, took about 50min with support to figure out what to do to fix the problem so it was worth it.

Jim

[Former Member]

The date part is working however the time was causing errors so I have seperated the values out to two differnt displays. As I stated the date is file here is the problem with the time field.

it is a string of 6 characters the informtion is stored as ' 1100 ' without the quotes so I have a space in the front and the back. here is my formula

stringvar t:= {TAGDTL.USER_DEF_TEXT_2}; //or database field

numbervar h:= tonumber ( t[1 to 3] );

numbervar mm:= tonumber ( t[4 to 6] );

time(h, mm, 0)

and now I get the following error while the time(h,mm,0) is highlighted

Minutes must be between 0 and 59

[former_member260594]

Dom,

Just change the range you are extracting to avoid the spaces from 1 to 3 > 2 to 3 and from 4 to 6 > 4 to 5

[Former Member]

Sorry, I forgot to say I tried that. 2:3 and 4:5 however it still gets the same error. I also looked to see if there was any bad data ie. blanks and the data is clean... Still get the same error message. this one has got me so bad, I have paid for a support call and am waiting for a response.

Edited by: James Fulton on Oct 9, 2008 5:58 PM

[former_member260594]

Hmmm, you might want to try using the Trim function to ensure that the spaces are rmoved and then use the ranges of 1:2 and 3:4

[Former Member]

Hello James,

this might help you, (direct from the help file of crystal)

//Crystal syntax
Local StringVar s := GroupName ({Orders.Order Date}, "monthly");
If IsDate(s) Then
     CDate(s)
Else
     CDate(0,0,0)

Returns the Date value May 1, 1998 if the GroupName field value is "May - 1998". Returns the null Date (a non-printing Date value) if the GroupName field value is "Others".

Comments

This function is similar to the Visual Basic function of the same name, except that it works specifically with Crystal Report's Date type which holds date values only.

Regards

Jehanzeb

[Former Member]

it highlights the monthly and says a group condition is not allowed here

this is the equation

local stringvar s := Groupname({TAGDTL.USER_DEF_TEXT_1},"monthly");

if isdate(s) then

cdate(s)

else

cdate(0,0,0)

Edited by: James Fulton on Oct 8, 2008 5:10 PM