Help with Date Format

WindInFace · #1 (**permalink**) 03-15-10, 15:33

Hi,

DB2 newbie here.

Can someone please help me format

Code:

SELECT CHAR(current timestamp)
FROM sysibm.sysdummy1

from '2010-03-15-11.30.52.607770' to

'03/15/2010 11:30:52.607770 am/pm?

schintala · #2 (**permalink**) 03-15-10, 17:01

You may try some thing like this

SELECT CHAR(CURRENT_DATE, USA) || ' ' || CHAR(CURRENT_TIME, USA)
FROM SYSIBM.SYSDUMMY1;

dav1mo · #3 (**permalink**) 03-15-10, 17:04

Something like:

Code:

SELECT CHAR(DATE(CURRENT TIMESTAMP)) || ' ' || CHAR(TIME(CURRENT TIMESTAMP))
                                                                            
   FROM SYSIBM.SYSDUMMY1

You would be best off perusing the SQL Reference manual for how to manipulate dates and times into the format you want.
Dave

WindInFace · #4 (**permalink**) 03-15-10, 17:15

Thank you dav1mo & schintala!

Lenny77 · #5 (**permalink**) 03-15-10, 17:25

The universal solution:

Code:

select char(current date) || ' ' || 
case 
when hour(current time) < 12 
then char(current time) || ' AM' 
when hour(current time) = 12 
then char(current time) || ' PM' 
when hour(current time) = 0 
then char(current time + 12 hour) || ' AM' 
when hour(current time) > 12  
then char(current time - 12 hour) || ' PM' 
end as "AM/PM Timestamp"
from sysibm.sysdummy1

Result:

Quote:

AM/PM Timestamp
2010-03-15 04.25.23 PM

Lenny

tonkuma · #6 (**permalink**) 03-15-10, 18:37

Quote:

from '2010-03-15-11.30.52.607770' to

'03/15/2010 11:30:52.607770 am/pm?

The issues of provided solutions are ignoring fractional seconds(or microseconds), like Lenny's Result showed.
If you dont mind the issue, that's OK.

To keep fractional seconds, you can use TIMESTAMP_FORMAT(synonym is TO_CHAR) function with rich format-string which is available DB2 9.5 or later for LUW.
(DB2 Version 9.1 for z/OS and DB2 for i Version 6 Release 1 don't support format strings 'AM', 'PM' and 'HH12'.)

Here is an example.

Code:

------------------------------ Commands Entered ------------------------------
SELECT VARCHAR_FORMAT( current_timestamp , 'mm/dd/yyyy hh12:mi:ss.nnnnnn AM' )
  FROM sysibm.sysdummy1;
------------------------------------------------------------------------------

1                              
--------------------------------------------------
03/16/2010 06:14:01.214000 AM                     
 
 
  1 record(s) selected.

tonkuma · #7 (**permalink**) 03-15-10, 19:01

An example without using VARCHAR_FORMAT is...

Code:

------------------------------ Commands Entered ------------------------------
SELECT CHAR( DATE(CURRENT_TIMESTAMP) , USA )
       || ' ' ||
       INSERT( CHAR( TIME(CURRENT_TIMESTAMP) , USA )
             , 6
             , 0
             , ':' || SUBSTR( CHAR(CURRENT_TIMESTAMP) , 18 , 9 )
             )
  FROM sysibm.sysdummy1;
------------------------------------------------------------------------------

1                            
-----------------------------
03/16/2010 06:57:30.376000 AM

  1 record(s) selected.

Lenny77 · #8 (**permalink**) 03-16-10, 12:07

With small change:

Code:

select char(current date, usa) || ' ' 
|| case 
        when hour(current time) < 12 
               then char(current time) || ' AM' 
        when hour(current time) = 12 
               then char(current time) || ' PM' 
        when hour(current time) = 0 
               then char(current time + 12 hour) || ' AM' 
        when hour(current time) > 12  
               then char(current time - 12 hour) || ' PM' 
   end   as "AM/PM Timestamp"
from sysibm.sysdummy1

Result could be:

Quote:

03/16/2010 11.07.27 AM

Lenny

tonkuma · #9 (**permalink**) 03-17-10, 12:21

Lenny,

Code:

when hour(current time) = 0 
               then char(current time + 12 hour) || ' AM'

would be never executed.

Because, there is a following description in the manual "DB2 SQL Reference".

Quote:

In general, the value of the case-expression is the value of the result-expression
following the first (leftmost) when-clause that evaluates to true.

And, the condition "hour(current time) = 0" is included in the first condition "hour(current time) < 12".

Lenny77 · #10 (**permalink**) 03-17-10, 15:46

Quote:

Originally Posted by tonkuma

Lenny,

Code:

when hour(current time) = 0 
               then char(current time + 12 hour) || ' AM'

would be never executed.

Because, there is a following description in the manual "DB2 SQL Reference".

And, the condition "hour(current time) = 0" is included in the first condition "hour(current time) < 12".

Thank you, tonkuma !

I could not understood what has to be wrong, and you find !
Also:

Code:

select char(current date, usa) || ' ' 
|| case 
        when hour(current time) = 0 
               then char(current time + 12 hour) || ' AM' 
        when hour(current time) < 12 
               then char(current time) || ' AM' 
        when hour(current time) = 12 
               then char(current time) || ' PM'       
        when hour(current time) > 12  
               then char(current time - 12 hour) || ' PM' 
   end   as "AM/PM Timestamp"
from sysibm.sysdummy1

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech