SQL Time in 24 Hours

["... you mean these fans?"]

Hi,

 

I need my SQL results in a 12 hr clock, not 24 hr clock.

 

I tried Hour( ) and Minute ( ) but there was not change.

 

I had to change my "Time" field to a "GetAsText" field so I could see my time as 9:00 AM or 9:00 PM.

 

But now I can not sort the SQL results.

 

please see pic.

 

Any assistance I would be grateful.

 

Thank you.

 

Tom :-)

SQL 24Hr.pdf

[comment]

Just curious - wouldn't:

 

SELECT Description, EventTimeCalc
FROM Events

WHERE ...
ORDER BY EventTime

 

work for you?

["... you mean these fans?"]

Hi Comment,

 

That was the first thing I tried, but the SQL Select statement throws a " ? "

 

I guess the "Ordered Fields" must be contained within the SQL ??

 

Anyway … any ideas I would be grateful.

 

Thank you.

[comment]

It works for me, although I am testing this in v.11 using a plugin. Still, I believe it should work as advertised - and it is so advertised in FMI's SQL documentation. Why don't you eliminate all other possible causes (such as the WHERE clause) to see if the error is indeed caused by what you think it is.

 

BTW, I don't think that the sort order is being "ignored": what you show seems to be ordered in alphabetical order - as you would expect when sorting by a text type field.

[comment]

Another thought: if =

 

SELECT Description, EventTimeCalc, EventTime
FROM Events

WHERE ...
ORDER BY EventTime

 

does work for you, try changing it to =

 

SELECT Description, EventTimeCalc, LEFT(EventTime, 0)
FROM Events

WHERE ...
ORDER BY EventTime

["... you mean these fans?"]

Hi Comment,

 

It works … thank you.

 

Now I have the " - " to cancel out after the AM / PM. Please see attached .pic

 

ExecuteSQL ( " 

 

Select Description, EventTimeCalc, Left('EventTime',0)

 

From Events  

 

WHERE ( ? BETWEEN EventStart AND EventEnd )

 

Order By EventTime ASC " ; " - " ; "" ;

 

cDateOfFirstPortal + 27 )

 

There's got to be a better way to handle this.  Hmm … is there a function in SQL that would convert the time field ??  I've been researching the net and googling and review 3wSchool but to no avail.

 

Any insight here I would be grateful.

 

Tom :-)

[comment]

Now I have the " - " to cancel out after the AM / PM.

 

Why don't you simply substitute it out? Something like:

Substitute ( SQLresult & ¶ ; " - ¶" ; ¶ )

There's got to be a better way to handle this.  Hmm … is there a function in SQL that would convert the time field ??

 

 

Not sure if it's any better, but this works for me:

STRVAL(MOD(HOUR(Timefield)-1,12)+1)
+':'+
STRVAL(RIGHT('0'+STRVAL(MINUTE(Timefield)),2))
+' '+
CASE WHEN Timefield < TIME '12:00:00' THEN 'AM' ELSE 'PM' END

["... you mean these fans?"]

Hi Comment :-)

 

Thank you ... thank you … thank you.

 

Now I've got something to take apart and learn.  This is excellent!!

 

Tom :-)

 

 

ExecuteSQL ( " 

 

Select Description, 

 

STRVAL(MOD(HOUR(EventTime)-1,12)+1)

+':'+

STRVAL(RIGHT('0'+STRVAL(MINUTE(EventTime)),2))

+' '+

CASE WHEN EventTime < TIME '12:00:00' THEN 'AM' ELSE 'PM' END

 

From Events  

 

WHERE ( ? BETWEEN EventStart AND EventEnd )

 

Order By EventTime Asc " ; "  -  " ; "" ;

 

cDateOfFirstPortal + 27  )