Hi.
How can format a TIME column in a query to display only hours and minutes (embedded HSQLDB)?
Thanks.
As far as I know, you can’t apply number formats to queries permanently. You can do this in the user interface only (form & reports).
Not easy with this kind of line:
CASE WHEN DAY_ID = 1 THEN ('[' || TIME_BEGIN || ' -' || TIME_END || '] ' || LASTNAME || ' ' || FIRSTNAME) END AS MONDAY
MONDAY
[08:00:00 - 10:00:00] Doe John
[10:00:00 - 12:00:00] Harrigan Stephanie
The seconds not displayed would save space in the page width for a printed document.
How about LEFT(TIME_BEGIN,5)?
It works. But in doing so, is the data type still TIME?
Is this a time?
The result of a concatenation is always text.
@Primus ,
you would usually use the function TO_CHAR to format a DATE, TIME or TIMESTAMP field.
the result is a string but the fields TIME_BEGIN and TIME_END are not altered and remain as type TIME.
case
when day_id = 1 then
to_char(TIME_BEGIN, '[H:mm - ')
||
to_char(TIME_END, '[H:mm] ')
||
lastname
|| ' ' ||
firstname
end
monday
1 Like
@cpb : The fields TIME_BEGIN and TIME_END won’t appear in your query as they won’t appear in the other queries. There will appear a new string, which is the result of a concatenation. If somebody needs the time fields as time it could only be formatted in GUI, but not formed the right way in a query together with ||
select
TIME_BEGIN,
TIME_END,
case
when day_id = 1 then
to_char(TIME_BEGIN, '[H:mm - ')
||
to_char(TIME_END, 'H:mm] ')
||
lastname
|| ' ' ||
firstname
end
monday
from
"MyTable"Ok. Now I have:
[08:00 - 10:00] Doe John
The reason I asked was to find out whether this could have any impact on the calculation of durations, intervals, etc.
Thanks for the clarification.
Yes, it doesn’t really matter anyway, since it’s a request for paper printing only. But it let me know at what level to define a “truncation” without compromising anything.
@Primus,
if you do not select the time fields then obviously they will not be available!!!
.
my first post was an sql statement and not an sql query.
.
my second post is an sql query, the time fields are selected and therefore will be available.
.
all you have to do to satisfy your doubts is give it a try.