changing varchar into date

[andreadd]

CR8.5 developer SQL2000 SP4

I am writing a login report and the information of login date and time is contained in 1 field called login and it is 14 character varchar.

the raw data looks like this: 20050123 07:32 - for login of 1/23/2005 at 7:32 a.m.

What I am hoping to do is grab the first 8 characters of this field and convert it into a date and then take the last 5 characters and convert that into a time. seperate formulas.

I havent done much of this type of conversion so I am not sure how to go about it and would greatly appreciate some pointers.

Thanks

 

[lbass]

//{@date}:
local stringvar x := {table.stringdatetime};
date(val(left(x,4)),val(mid(x,5,2)),val(mid(x,7,2)))

//{@time}:
local stringvar x := {table.stringdatetime};
time(val(mid(x,10,2)),val(mid(x,13,2)),0)

Place these on the report, and then use format date/time to get the desired display.

-LB