convert string to time

[SMosley804]

Crystal Reports ver 11
IBM DB2


I have a Timestamp field that is in string format. I need to convert to a date time

example

10810021400000

should be

10/02/2008 2:30 PM

please help

 

[kskid]

It is not a Julian date, which would be 2454742.10417

Are you sure you have the correct string value for the date? I can see the 081002 being YYMMDD but am not sure about the leading 1 and the trailing 1400000 unless it's suppose to be 1430000. How is the century converted?

 

[SMosley804]

its not a julian date. not really sure what it is. The string is correct.

so far I 've come up with if you add 1900 to the 1st 3 character you will get the year the next 2 are the 2dig month, the next 2 are the 2 digit day etc...


10810021400000

so working with the string above:
108=2008
10=10
02=02

14=2
00=00
00=00

so 108100214000000 is the same as 10/02/2008 02:00 PM

 

[kskid]

Create a formula

//@convertdate

local numbervar yr;
local numbervar mo;
local numbervar dy;
local numbervar hr;
local numbervar mi;
local numbervar sec;

//@convertdatestr
if isnull({table.datestr) then
datetime(1900,1,1,0,0,0)
else
datetime(
tonumber(mid({table.datestr},1,3))+1900,
tonumber(mid({table.datestr},4,2)),
tonumber(mid({table.datestr},6,2)),
tonumber(mid({table.datestr},8,2)),
tonumber(mid({table.datestr},10,2)),
tonumber(mid({table.datestr},12,2))
)

 

[SMosley804]

am I creating 2 formulas or just the one?

 

[kskid]

Sorry. I thought I cleaned it up.

Just the one on the bottom, //@convertdatestr

 

[SMosley804]

thanks kskid! works like a charm

 

[chilakase]

how can i print back side of the page ,suppose detailed section i want to print on the back side of the page is ther any formula

 

[lbass]

chilakase,

Please start a new thread.

-LB