Convert SQL Time(7) field in desktop report

[Mark Williams]

I have a desktop report that retrieves data from an SQL Data Source.

One of the SQL fields is a Time(7) field and I am having trouble converting it to 'HH:MM pm' format on the desktop report.

I've created a calculated field on the desktop report as follows:
Calc_Time = totime(toseconds(left(convert_type(Record_Time,"C"),5)),11,0)

This works if the record_time is 12:01pm-12:59pm, but for times from 1:01PM - 11:-59PM the calculated field displays the time as am instead of pm.

Appreciate any suggestions.

[armasoft]

Your code works for me...

dim time1 as c ="13:12:12.1111111"
?totime(toseconds(left(convert_type(time1,"C"),5)),11,0)
= "1:12 pm"

Don't have an SQL db handy, but that's the format I believe time(7) returns by default. Are you sure your test values are > 13:00:00 ?

[madtowng]

Hi Mark,

I'm not sure if I completely understand what you're trying to accomplish, but I'll give it the good ol' effort.
I'll give two examples, both using the TIME() function, which I think provides the result you want.
Please let me know which is most helpful, or even where I went wrong with what you're trying to do.

Gregg
Example A: (as I read it, assumes record_time to be 1:01PM,)

dim Record_Time as y
Record_Time = {1:01PM}
?time("h:0mAM",Record_Time)
= "1:01PM"

Example B: (based on armasoft's response)

dim Record_Time as y
Record_Time = {13:01:12.1111111}
?time("h:0mAM",Record_Time)
= "1:01PM"

[Mark Williams]

I'm at a loss for an explanation of the results I am getting here.

I am getting two different results on two different machines. Each machine is running Alpha Build 7150-5538. I received the same behavior on older builds as well.

When I enter the calculated field definitions in the code editor on each machine, I receive the expected results:

dim time1 as c ="13:15:00"
?totime(toseconds(left(convert_type(time1,"C"),5)),11,0)
= "1:15 pm"

dim Record_Time as y
Record_Time = {13:15:00}
?time("h:0mAM",Record_Time)
= "1:15PM"

I created a test report and placed each of the above calculated fields on the report and receive different results on each machine.

Computer 1 report:

474038_Report_1.jpg

Computer 2 report:

474038_Report_2.jpg

The report is being run from the desktop. The Record_Time field is in a SQL table defined in the datasource of the report.

If it helps, here is the sql data it is referencing:

474038_Time_Field.jpg

[madtowng]

Hi Mark,

I'm trying to duplicate what you're getting, but odds are I am doing things slightly different than you are.
Could you post the structure of your table and confirm which version of sql you're using?

[Mark Williams]

Hi Mark,

I'm trying to duplicate what you're getting, but odds are I am doing things slightly different than you are.
Could you post the structure of your table and confirm which version of sql you're using?

Thanks for taking a look at this...

I am using Microsoft SQL Server v17.9.1

Here's a screenshot of the field properties:

ss1.jpg

[Ted Giles]

You are on the right track Gregg.
Might be the SQL version, or possibly the extra stuff installed when you implement a new build.
I think it's one of the Msoft redistributables.

[armasoft]

Times both show as 'pm' for me on a report. I have no formatting on the fields in the report.
Is there any difference between the 2 PCs for regional location/date time settings?

testtime = totime(toseconds(left(convert_type("13:15:00","C"),5)),11,0)
testtime2 = time("h:0m am",{13:01:12.1111111})

[Ted Giles]

Mark, why are you using a Calculated field and not the database one?

[Mark Williams]

Mark, why are you using a Calculated field and not the database one?

Ted,

If I add the field it doesn't allow me to format it.

It displays as 01:15:00 00 pm

Thanks

[madtowng]

Hi Mark,

I did some extremely basic testing using things as I would set them up.

The table is setup with 2 fields, 1 for date, one for time.

etj24mw01.png

There is one row of data :

etj24mwrecords.png
If you have any questions, I have better contact info in the PM I sent you this morning.


I used 1 calculated field for the report :

etj24mwcalcedfield.png

This is the output from that report:

etj24mwreportoutput.png

I sent you a PM that has contact info if you have more questions.

[Ted Giles]

Try this in a calculated field.

time("0h-0m.am", Dtim) where Dtim is the time field from SQL. I have taken out the seconds, but the function is pretty simple.

Examples below

12-05 am
11-05 pm

"time" will be the result of the expression as a Character filed, so then you can chop it up using Left/Right etc.

[Mark Williams]

Try this in a calculated field.

time("0h-0m.am", Dtim) where Dtim is the time field from SQL. I have taken out the seconds, but the function is pretty simple.

Examples below

12-05 am
11-05 pm

"time" will be the result of the expression as a Character filed, so then you can chop it up using Left/Right etc.

This has got me stumped...

I copied the workspace directory to six different computers on different networks in different locations. I confirmed the Alpha settings relating to dates and times matched on all 6 machines. I also confirmed the Windows 10 system time and regional settings all matched on all 6 machines. Lastly, I updated all versions of Alpha to 7158-5539 (also tried various earlier versions and the results did not change). Some machines have both developer and runtime installed on them and the results didn't change based on whether it was run from developer or runtime.

I used the three recommended calculated fields as suggested in this thread.

The results:

Three machines displayed it as follows:

Time_Display2.jpg

The other three machines displayed it as follows:

Time_Display1.jpg

[Mark Williams]

I may have stumbled upon something....

I edited the report to also include the unformatted sqldatasource field. For some reason, this field is displayed differently on the two different scenarios.

I haven't figured out why the original time field is displayed differently and how to correct this to see if it is the issue.

See screenshots below...

Report displays this way on three machines:
Time_Display3.jpg
Report displays this way on the other three machines:
Time_Display4.jpg

[Mark Williams]

Took this a step further and ran the following in the code editor:

dim time1 as c ="13:15:00.0000000"
?totime(toseconds(left(convert_type(time1,"C"),5)),11,0)
= "1:15 pm"

dim time1 as c ="01:15:00 pm"
?totime(toseconds(left(convert_type(time1,"C"),5)),11,0)
= "1:15 am"

Now just have to figure out why the sqldatasource->record_time displays differently on different machines...