formula with string

[tttggg]

I have 2 string fields SALESDT & SALESTM which represents the date and time.

I need to display these two fields as a string as following
yyyy/mm/dd hh:mm:ss

I’m trying to do this but I’m not successful. Any help with this is much appreciated.
Working with CR 8.5 & DB2

 

[bdreed35]

Create a formula for each that uses the totext function to convert them to string:

totext({table,field},"yyyy/MM/dd hh:mm:ss")

~Brian

 

[lbass]

How do the two fields currently display as strings?

-LB

 

[tttggg]

currently the fields displays
SALESDT : 20080208
SALESTM : 024559

i wanted it to be like 2008/02/08 02:45:59

 

[lbass]

Try:

datetime(date({table.salesdt}),time({table.salestm}))

-LB

 

[tttggg]

Hi Brain
I tried totext({table,field},"yyyy/MM/dd hh:mm:ss")
But i throws the following error:

"Too many arguments have been given to this function

 

[PatriciaObreja]

Left({SALESDT}, 4) & "/" & Mid({SALESDT}, 5, 2) & "/" & Mid({SALESDT}, 7, 2) & " "
& Left({SALESTM}, 2) & ":" & Mid({SALESTM}, 2, 2) & ":" & Mid({SALESTM}, 4, 2)

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[tttggg]

Hi Lb
Thanks for the concern

I tried it and there were "No Errors" but when i add it to the report it shrows a Error, "Bad date format String"
Am i Missing anything here?

 

[tttggg]

Thanks PatriciaObreja
It works.thanks a lot....

 

[lbass]

I should have tested it. If you want the field to actually be a datetime datatype, then adapt Patricia's solution like this:

datetime(val(Left({SALESDT}, 4)), val(Mid({SALESDT}, 5, 2)),val(Mid({SALESDT}, 7, 2)), val(Left({SALESTM}, 2)),val(Mid({SALESTM}, 3, 2)),val(Mid({SALESTM}, 5, 2)))

Then you can choose how to format it by going to format date, etc.

-LB