cancel
Showing results for 
Search instead for 
Did you mean: 

how to convert military time to GMT

0 Kudos

My report has a time field that has values like:

Arrival Time
1430
1245
830
1600
1750
1900
700
1330
1250
1540
1525
1750

i need to convert it to a time field like 2:30 PM, 1245PM, 8:30 AM etc..

How do i do that?

I tried creating a new variable: =ToDate(FormatNumber([Arrival Time];"#");"yyyyMMdd")

It Gave me an error.

Accepted Solutions (1)

Accepted Solutions (1)

former_member207052
Active Contributor
0 Kudos

The reason why you were getting the error might be because of the fact that the field is not a date /time field and hence for the same reason, even your DBA might need to write some code to get the format you require.

But you can achieve what you want in web-I or through universe.

Web-I formula below:


=FormatNumber( (If ([Arraival Time]<=1259) Then [Arraival Time] Else [Arraival Time]-1200);"##-:##") +" "+ (If (([Arraival Time]))<1200 Then "AM" Else "PM")

Tested & Works


0 Kudos

Man you are Good!! Thank you very much

Answers (1)

Answers (1)

CdnConnection
Active Contributor
0 Kudos

Neihi,

     The most efficient way would be to create additional Arrival Time objects within the Universe for the desired format required such as GMT, EST, CST, ....   This way the users can use the objects required.

Regards,

Ajay

0 Kudos

But how do you do that?

CdnConnection
Active Contributor
0 Kudos

Neihi,

     It all depends on you back-up database engine.  You will need to check with your DBA for the proper SQL statement for you Database.

See sample of ORACLE SQL converting GMT to EST

NEW_TIME(TABLE.ARRIVAL_TIME,'GMT','EST') AS ARRIVAL_TIME_EST


Regards,

Ajay

0 Kudos

Our database is SQL server.

CdnConnection
Active Contributor
0 Kudos

You need to ask you SQL Server DBA, I don't know the SQL?

Ajay