timestamp format incorrect?

db2user24 · #1 (**permalink**) 10-26-10, 14:37

Hi,

Can you please tell what I'm doing wrong here? Thanks!!

db2 "select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(CHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1"

1
--------------------------
SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007

This looks good --

db2 "select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(CHAR('00') || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1"

1
--------------------------
2010-10-26-00.00.00.000000

1 record(s) selected.

tonkuma · #2 (**permalink**) 10-26-10, 14:54

CHAR(HOUR(CURRENT TIMESTAMP)) includes trailing blanks, like this:

Code:

------------------------------ Commands Entered ------------------------------
select CHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00') from sysibm.sysdummy1;
------------------------------------------------------------------------------

1                
-----------------
3          .00.00

  1 record(s) selected.

Use VARCHAR rather than CHAR, like...

Code:

------------------------------ Commands Entered ------------------------------
select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(VARCHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1;
------------------------------------------------------------------------------

1                         
--------------------------
2010-10-27-03.00.00.000000

  1 record(s) selected.

db2user24 · #3 (**permalink**) 10-26-10, 15:04

thanks, I get this now --

db2 "select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(VARCHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1"
SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

btw, am using version 8.2 Db2 UDB

tonkuma · #4 (**permalink**) 10-26-10, 15:13

Quote:

btw, am using version 8.2 Db2 UDB

So, try RTRIM(CHAR(HOUR(CURRENT TIMESTAMP))), like ...

Code:

------------------------------ Commands Entered ------------------------------
select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(RTRIM(CHAR(HOUR(CURRENT TIMESTAMP))) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1;
------------------------------------------------------------------------------

1                         
--------------------------
2010-10-27-04.00.00.000000

  1 record(s) selected.

tonkuma · #5 (**permalink**) 10-26-10, 15:45

If you want to get truncated timestamp, here is an example.

Code:

------------------------------ Commands Entered ------------------------------
select TIMESTAMP( SUBSTR(CHAR(CURRENT TIMESTAMP), 1, 14) || '00.00' ) from sysibm.sysdummy1;
------------------------------------------------------------------------------

1                         
--------------------------
2010-10-27-04.00.00.000000

  1 record(s) selected.

db2user24 · #6 (**permalink**) 10-26-10, 16:29

Quote:

Originally Posted by tonkuma

If you want to get truncated timestamp, here is an example.

Code:

------------------------------ Commands Entered ------------------------------
select TIMESTAMP( SUBSTR(CHAR(CURRENT TIMESTAMP), 1, 14) || '00.00' ) from sysibm.sysdummy1;
------------------------------------------------------------------------------

1                         
--------------------------
2010-10-27-04.00.00.000000

  1 record(s) selected.

great! thanks..this is perfect!

Internet Services

Web Development

Digital Marketing

Consumer Tech