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]

Hi,

You never disclosed the string that you expect to be 1 pm!

 

[talibm]

Sorry about that. So for example the string is "11:30" should be 11:30 AM and ctime give me that, but when the string is "1:00" ctime give me 1:00 AM and I want it to show 1:00 PM. For all the fields before noon ("12:00) we need AM and from "12:00" on we need PM. thanks

talib

 

[pmax9999]

You say that if the time field is less than 12:00 you want it to be AM, but then say that "1:00" should be 1:00 PM which is inconsistent.

What data indicates that the time should be PM? If "1:00" is 1:00 PM, how would 1:00 AM be displayed in the database, ie what is the difference between how 1:00 AM and 1:00 PM is stored?

If you answer these two questions the solution to your query will become obvious.

Cheers
Pete

 

[talibm]

Hello Pete,

The data comes from a web application we use for interviewing clients. The field, tag q4, is a string that has the time 1:00 and another field, tag 4a, has the prefix am or pm. I have converted the string for the time using ctime only using q4 and not using qa for the prefix. ctime formats everything before 12:00 as am and from 12:00 to 12:59 as pm. So my dilemma is bigger than I first thought. I did try concatenating the string fields q4 and q4a and then converting it to a time field but that didn't work for me. Thanks for any help you can give.

talib

 

[SkipVought]

Convert the time to date/time.

If it's to be pm, then add .5 to the converted time (that is half a day, so 1:00 becomes 13:00 which can be formatted 1:00 pm)

 

[pmax9999]

Try this formula:

Time({Table.Time_Field} + {Table.AMPM_Field})

Hope this helps

Cheers
Pete

 

[talibm]

Hello Pete and Skip,

I tried the concatenation that you suggested Pete, but I can't convert that to a time filed. The reason for the conversion is that I need to calculate the total time of the interview. So I have an interview start time and and interview end time. I am trying to convert the string to a time format in order to get the total time for the interview.

Skip converting the time to datetime and adding .5 changed the prefix for both AM and PM. This have changed 11:00 am to pm and I need the time from 8:00 to 11:59 to be am and from 12:00 until 6:00 to be pm. I hope this helps. thanks

talib

 

[SkipVought]

adding .5 changed the prefix for both AM and PM."

Why are you adding .5 to the rows that have AM??? Did you not read my post completely?(tag 4a!)

 

[pmax9999]

Not sure what to say; I tested my formula before posting it and it definitely worked. Please explain the result and/or provide the error message was when you did as I suggested.

Please also post the exact formula you used.

If you want to use Skip's approach, you would need to use an If-Then-Else statement to conditionally add the .5 based on the AM/PM field. Of course it will change everything from AM to PM if you just add .5 to everything.


Cheers
Pete

 

[talibm]

Hi Pete and Skip,

I took Skip's suggestion and it worked just the way I had hoped(see below). Thank you for the time and knowledge . Now if I can get my datadiff formula to work I'll be in business. Thanks again,
talib

Interview start time:
if {@start_time} >= Time(01,00,00) and {@start_time} <= Time(06,00,00) then DateTime (CurrentDate,{@start_time} ) + .5
else
datetime(CurrentDate,{@start_time})

Interview end time
if {@end_time} >= time(01,00,00) and {@end_time} <= Time(06,00,00) then DateTime (CurrentDate,{@end_time} ) + .5
else
datetime(CurrentDate,{@end_time})

 

[talibm]

Hello guys,

one last hurdle. My datediff formula, DateDiff ("n", {@end_date_time}, {@start_date_time}), is returning 0.00. What am I missing here? thanks

talib

 

[pmax9999]

Your approach of assuming a time between 1:00 and 6:00 as being PM and everything else as being AM is flawed. What if an interview went a few minutes late and finished at 6:01 pm; it would assume it to be am rather than pm. And, what if circumstances change in the future and the working day is extended to 6:30 pm?

Also, using your formula, anything that happens between midday and 1:00 pm will show as AM.

You say you have a field that indicates am or pm; wouldn't it be much better to use it?

In regard to your DateDiff problem your formula looks OK but you are referencing other formulas; we would need to see the contents of those other formulas to be able to assist further.

As a starting point in the troubleshooting process I would suggest you put all 4 formulas (2 start date/times, 2 end date/times) on the report so you can see they are returning the data you think they are.

Cheers
Pete

 

[SkipVought]

I totally agree with Pete!

Your logic is flawed.

Use the field contains the AM/PM indicator.

 

[talibm]

Hi guys,

The reason for the start time and end time reflect the office hours for this program. 8 am to 5 pm. The building opens at 8 and closes a 5. First let me say that all the times show as am, except 12:00 to 12:59, when I converted the string to a time field. So that time period is omitted from the formula. Any start time or end between 1 and 6 would be pm and any start or end time before 12:00 would be am. The data that i am getting is correct.

Having said that I will try concatenating the time string and and the am/pm prefix. It would seem that if this works I would be faced with the same problem, referencing other formulas.

thank you both for your help. I will keep you posted on my progress.

talib

 

[SkipVought]

Hmmmmmm????

"Any start time or end between 1 and 6 would be pm and any start or end time before 12:00 would be am."

That statement is incorrect! Any time including 12:00 and following would be PM!

What happens when work hours are extended past 6:00 pm? Let's say business booms and you start running 2 or 3 shifts?

Don't paint yourself into a corner with ASSUMPTIONS.

 

[AndyMc]

I think Pete and Skip are suggesting you do something like this:

Interview start time:
if {q4a} = "pm" then DateTime (CurrentDate,{@start_time} ) + .5
else
datetime(CurrentDate,{@start_time})

Interview end time
if {q4a} = "pm" then DateTime (CurrentDate,{@end_time} ) + .5
else
datetime(CurrentDate,{@end_time})

 

[talibm]

Hi guys,

Maybe I need to take a step back and give you more background info. The data that I have is entered via a web application that was created for us by a 3rd party. There are 3 tables that I am using. The interview table, the question table and the answer table. The interview date , start time , prefix are in interview section. the tag id(s) are found in the question table and the answer table hold the responses associated with the tag id. The answer field is a memo field and contains all responses. The section are the tab where the data is entered on the web pageFor example:

Start time - if {Section.Name} = "Preamble" and {Question.Tag} = "q4" then ctime({Answer.Answer}). the prefix tag id is 4a and the value in the answer field is 8641 for am or 8642 for pm

End time - if {Section.Name} = "Motivation" and {Question.Tag} = "q7" then ctime({Answer.Answer}). the prefix tag id is 7a and the value in the answer field is 8641 for am or 8642 for pm

I have tried each of the most recent suggestions, I didn't get the correct values for the start time and end time. Only when I used my flawed logic, modification of Skip's original suggestion, did I get the correct start time and end time. At this point I am stuck. thanks

talib

 

[SkipVought]

your clarification is like mud!

What is this supposed to mean?

then ctime({Answer.Answer}). the prefix tag id is 4a and the value in the answer field is 8641 for am or 8642 for pm

You're supposed to understand your data structure. Please explain what it is that you just stated in a clear, concise and complete manner. Seems as if we have been jerked around by your lack of clarity.

 

[talibm]

I am trying to explain. Maybe not well enough. Anyway thanks for your time.

talib