Convert SQL Time(7) field in desktop report

[madtowng]

Mark,
See what happens if you remove the 0 left of the h:

[Ted Giles]

Ok, this might sound odd, but try changing the name of the result.
You are using Calc_Time then 2 and 3
Alpha can be funny about naming conventions and anything over 8 char in a name can cause issues, especially with Indices.
Calc_Time is 9 characters.

Try it with a simple calc name such as abc1, def2, hij3

[Mark Williams]

Mark,
See what happens if you remove the 0 left of the h:

Gregg,

I received the same results other than the preceding '0' being omitted.

Thanks

[Mark Williams]

Ok, this might sound odd, but try changing the name of the result.
You are using Calc_Time then 2 and 3
Alpha can be funny about naming conventions and anything over 8 char in a name can cause issues, especially with Indices.
Calc_Time is 9 characters.

Try it with a simple calc name such as abc1, def2, hij3

Ted,

Doing as you suggested resulted in the same results.

Thanks

[madtowng]

Mark,
I'm going to go back to the very beginning of this post.
The problem was Calc_Time = totime(toseconds(left(convert_type(Record_Time,"C"),5)),11,0) would return an incorrect time for anything between 1 pm and 11 pm.
I'm going to start with you are converting a character variable to a character variable.
Next, you're taking the 5 left characters of time1, for this example lets assume time1 = "1:15 PM" . This means you're taking "1:15 ", which you then convert to seconds.
That = 4500
you then convert 4500 to time which correctly returns 1:15 AM.

IF you dim time1 as Y = {1:05 PM}, then use time("0h:0m",{1:15 PM}) for the rest of your calculations IE: totime(toseconds(left(time("0h:0m",time1),5)),11,0), you should
get the expected result of "1:15 pm" .

[madtowng]

Mark, This reply is specifically to post #15, the result for the 2nd example is correct : left(time1,5) at this point = "01:15", which would be 1:15 am.

[armasoft]

You're chopping off the 'am'/'pm' segment when you perform your left function. This is OK if you define the value using 24 hour clock, but will obviously cause problems if using 12 hour option.

?convert_type(time1,"C")
= "01:15:00 pm"

?left(convert_type(time1,"C"),5)
= "01:15"

[Mark Williams]

I still cannot figure out why 3 test machines show the contents of the field SqlDatasource->Record_Time as "01:15:00:00 pm" and the other 3 test machines show the contents as "13:15:00 0000000".

I can create a calculated field to work with either field result but that doesn't explain why they are different to begin with.

The only common denominator that I have found between them is the three machines that display the field as 01:15:00 00 pm have both MS Sql Express and Alpha Aplication Server installed on them. The other three machines do not. Suppose that could make a difference?

If needed, I suppose I could do a conditional field that checks the results of SqlDatasource->Record_Time and if it contains "m" then display calculated field #1, otherwise display calculated field #2. I hate to go this route but may have to.

Thanks

[Ted Giles]

Looks like that may be the only option, Mark.
I assume you have tried exporting a correctly functioning report and importing it to one of the other machines with the problem?
Not just copying the app, but export and import.

[madtowng]

Sometimes a second pair. of eyes can make a difference.

[Ted Giles]

How is the time being set?

[Mark Williams]

How is the time being set?

Ted,

The fields are populated from users cellphones while in the field. Our web app does GPS geocoding and captures the coordinates, date, time, etc.

Thanks

[Mark Williams]

So... In my quest to figure this out, I found something interesting that explains the difference between the way two machines display the time differently. I don't know how to fix it but perhaps it will make more sense to others?

I accessed the same workspace files and data on both computers and then viewed the datasource fields. For some reason, the two machines show the date and time fields with different definitions.

Machine1:
machine1.jpg

Machine2:
machine2.jpg

Alpha allows you to override the field definition so I changed the field definitions on machine 1 to match machine 2:
machine1b.jpg

After doing this, the time is still displayed as:
Machine1 - 01:15:00 00 pm
Machine2 - 13:15:00.0000000

I then deleted the connection string and datasource definitions on both computers and recreated them from scratch and received the same results.

Does this help to narrow it down?

Thanks

[Ted Giles]

Are the processors the same on the different machines?
I'm stumped, so suggest you now contact Selwyn or Lenny at Alpha to see if they have any ideas.

[armasoft]

Could I suggest you try

totime(toseconds(convert_type(time1,"C")),11,0)

in your calculated field? I've removed the left(X,5) from the original equation. This returns 1:15pm for me using both input formats.