Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Convert SQL Time(7) field in desktop report

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert SQL Time(7) field in desktop report

    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.

  • #2
    Your code works for me...

    Code:
    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 ?

    Comment


    • #3
      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,)
      Code:
      dim Record_Time as y
      Record_Time = {1:01PM}
      ?time("h:0mAM",Record_Time)
      = "1:01PM"
      Example B: (based on armasoft's response)
      Code:
      dim Record_Time as y
      Record_Time = {13:01:12.1111111}
      ?time("h:0mAM",Record_Time)
      = "1:01PM"
      Gregg Schmidt

      Comment


      • #4
        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

        Comment


        • #5
          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?
          Gregg Schmidt

          Comment


          • #6
            Originally posted by madtowng View Post
            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

            Comment


            • #7
              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.
              Ted Giles
              Example Consulting - UK
              .

              sigpichttp://ec12.example-software.com//
              See our site for Alpha Support, Conversion and Upgrade.

              Comment


              • #8
                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?

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

                Comment


                • #9
                  Mark, why are you using a Calculated field and not the database one?
                  Ted Giles
                  Example Consulting - UK
                  .

                  sigpichttp://ec12.example-software.com//
                  See our site for Alpha Support, Conversion and Upgrade.

                  Comment


                  • #10
                    Originally posted by Ted Giles View Post
                    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

                    Comment


                    • #11
                      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.
                      Attached Files
                      Gregg Schmidt

                      Comment


                      • #12
                        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.
                        Ted Giles
                        Example Consulting - UK
                        .

                        sigpichttp://ec12.example-software.com//
                        See our site for Alpha Support, Conversion and Upgrade.

                        Comment


                        • #13
                          Originally posted by Ted Giles View Post
                          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
                          Attached Files

                          Comment


                          • #14
                            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

                            Comment


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

                              Code:
                              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...

                              Comment

                              Working...
                              X