| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
06-10-10, 03:36
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 52
|
|
|
Need help with time formating
|
|
Hi Guys,
I am getting the time format (HH:MM AM/PM) from a timestamp field by the following code
<CODE>
CHAR(time(th.T_STMP_MODFD),usa)</CODE>
But i want it in HH: MM: SS AM/PM.
Thanks for your help in advance.
Regards,
Magesh
|
|
06-10-10, 04:13
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 52
|
|
trying with this correct me if its wrong
<CODE>
concat(concat(CONCAT( SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),1,(LENGT H(CHAR(TIME(TH.T_STMP_MODFD),USA))-3),CODEUNITS32),':'),
SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD)),(LENGTH(CHAR (TIME(TH.T_STMP_MODFD),USA))-1),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNI TS32))
,SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),(LENGTH (CHAR(TIME(TH.T_STMP_MODFD),USA))-2),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNI TS32)
)
</CODE>
|
Last edited by mac4rfree; 06-10-10 at 04:55.
Reason: Got solution
|
06-10-10, 05:20
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
Code:
------------------------------ Commands Entered ------------------------------
SELECT th.t_stmp_modfd
, concat(concat(CONCAT( SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),1,(LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA))-3),CODEUNITS32),':'),
SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD)),(LENGTH(CHAR (TIME(TH.T_STMP_MODFD),USA))-1),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNITS32))
,SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),(LENGTH (CHAR(TIME(TH.T_STMP_MODFD),USA))-2),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNITS32)
) AS expr_1
, CHAR( INSERT( CHAR(TIME(th.t_stmp_modfd) , USA)
, 6
, 0
, ':' || SUBSTR(CHAR(th.t_stmp_modfd) , 18 , 2)
)
, 11
) AS expr_2
, LEFT(CHAR(TIME(th.t_stmp_modfd) , USA) , 5)
|| SUBSTR(CHAR(TIME(th.t_stmp_modfd) , JIS) , 6 , 3)
|| SUBSTR(CHAR(TIME(th.t_stmp_modfd) , USA) , 6 , 3)
AS expr_3
FROM (VALUES CURRENT_TIMESTAMP
, CURRENT_TIMESTAMP - 12 HOURS
) AS th(t_stmp_modfd)
;
------------------------------------------------------------------------------
T_STMP_MODFD EXPR_1 EXPR_2 EXPR_3
-------------------------- ------------------------- ----------- --------------
2010-06-10-18.17.12.318000 06:17:12 PM 06:17:12 PM 06:17:12 PM
2010-06-10-06.17.12.318000 06:17:12 AM 06:17:12 AM 06:17:12 AM
2 record(s) selected.
It looks that your expression(expr_1) worked well.
But, there will be shorter expressions(e.g. expr_2, expr_3).
|
|
06-10-10, 05:25
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
You can use "["CODE"]" and "["/CODE"]"(remove double quotations) for Code.
|
|
06-10-10, 10:14
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Shorter
Is this one not good:
Code:
select right(digits(hour(current timestamp)), 2) ||':' ||
right(digits(minute(current timestamp)), 2) ||':' ||
right(digits(second(current timestamp)), 2) ||' ' ||
case when hour(current timestamp)between 0 and 11 then 'AM'
else 'PM' end
from sysibm.sysdummy1
Result:
Lenny
|
|
06-10-10, 14:16
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
It would be not good from TIME('13:00:00') to TIME('23:59:59').
Because, hours part will show 13 to 23.
|
|
06-10-10, 15:27
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Correction
Quote:
Originally Posted by tonkuma
It would be not good from TIME('13:00:00') to TIME('23:59:59').
Because, hours part will show 13 to 23.
|
Thank you, tonkuma !
Trying to fix:
Code:
select right(digits(
case when hour(current timestamp) = 0
then 12
when hour(current timestamp) < 12
then hour(current timestamp)
else hour(current timestamp) - 12
end ), 2) ||':' ||
right(digits(minute(current timestamp)), 2) ||':' ||
right(digits(second(current timestamp)), 2) ||' ' ||
case when hour(current timestamp)between 0 and 11 then 'AM'
else 'PM'
end "EST Time"
from sysibm.sysdummy1
Result:
Lenny
|
|
06-10-10, 16:39
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
It'll depend on OP's requirement.
According to my brief test,
hours part in the result of CHAR( time-expression , USA) function showed as following table:
(I don't know wheather these results are common sense in USA.)
Code:
time-expression | CHAR(time-expression , USA)
----------------------+----------------------------
00:00:00 <-> 00:00:59 | 00:00 AM
00:01:00 <-> 00:59:59 | 12:01 AM <-> 12:59 AM
01:00:00 <-> 11:59:59 | 01:00 AM <-> 11:59 AM
12:00:00 <-> 12:59:59 | 12:00 PM <-> 12:59 PM
13:00:00 <-> 23:59:59 | 01:00 PM <-> 11:59 PM
|
Last edited by tonkuma; 06-10-10 at 17:00.
Reason: Change "Still not good." to "It'll depend on OP's requirement."
|
06-10-10, 17:27
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
00:00:00 <-> 00:00:59 | 12:00 AM <-> 12:59 AM
Lenny
|
|
06-10-10, 17:43
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
00:00:00 <-> 00:00:59 | 12:00 AM <-> 12:59 AM
|
No. Here is a test result.
(Tested on DB2 Express-C 9.7.2 on Windows.)
Code:
------------------------------ Commands Entered ------------------------------
SELECT time_expr
, CHAR(time_expr , USA) time_usa
, CHAR(time_expr , ISO) time_iso
FROM (SELECT TIME(time_char) time_expr
FROM (VALUES '00:00:00'
, '00:00:59'
, '00:01:00'
, '00:59:59'
, '01:00:00'
, '11:59:59'
, '12:00:00'
, '12:59:59'
, '13:00:00'
, '23:59:59'
) t(time_char)
)
;
------------------------------------------------------------------------------
TIME_EXPR TIME_USA TIME_ISO
--------- -------- --------
00:00:00 00:00 AM 00.00.00
00:00:59 00:00 AM 00.00.59
00:01:00 12:01 AM 00.01.00
00:59:59 12:59 AM 00.59.59
01:00:00 01:00 AM 01.00.00
11:59:59 11:59 AM 11.59.59
12:00:00 12:00 PM 12.00.00
12:59:59 12:59 PM 12.59.59
13:00:00 01:00 PM 13.00.00
23:59:59 11:59 PM 23.59.59
10 record(s) selected.
|
|
06-10-10, 18:00
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Something wrong: In USA 00:mm:ss doesn't exist.
Lenny
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Need help with time formating
