Convert String to Time

[talibm]

Hi All

I have created a report using Crystal Reports XI. The database was created using sql. The time field is a string and I converted it to a time field using ctime. the problem is that it displays 1:00 as AM when it is actually PM. I tried unsuccessfully to correct this using Format Field. The result I want is for time field < 12:00 to have AM prefix and time field => 12:00 to have PM prefix. Any help would be greatly appreciated. thanks

talib

 

[SkipVought]

Seems as if you have an un-normalized question table, so it would seem that each question in a row, would need its own set of start/end/AM PM fields. What a mess! It does not make sense that the Start Time and End Time would have different Section.Names??? Please explain that as well.

 

[talibm]

the section names represent the tab in the web page when the data is input. The Preamble section in the first section where the interview begins and the Motivation section is where the end time is located. The modified formula that i posted works. It give me the correct times, but I cannot get the datediff function. thanks

 

[SkipVought]

It's not rocket science. If all time values are stored as values between 00:00 and 12:00, then there must be some data element that indicates AM or PM.

So that's all you need to calculate a proper time value, as indicated above by andymc. Modify accordingly.

 

[pmax9999]

So far we have been attempting to provide a solution but based on only part of the information. If you still need assistance with this, the best thing you can do is provide some sample raw data, details for interpreting that data if it is not obvious and column names.

I am confident that we will then be able to provide a solution that works, and minimise frustration levels all round.

Cheers
Pete

 

[talibm]

Hi Pete,

Thanks for your help. I apologize for my lack of information at the outset. The tables that i am trying to get the data from were created from a web application. Each question has a tag/row and the response to all questions is collected in the answer field which is a memo field. Having said that I don't want to waste anymore of your time. My first thought when I got this request was to create a view. The would allow me to get all the data into one place and also define the field types. Then the crystal report will be a lot easier. Thank you again. Cheers!

talib