Create timestamp using month, day and year from table

db2user24 · #1 (**permalink**) 09-23-10, 15:32

Hi,

I have a table TEST with the columns startmonth, startday and startyear.

I want to create a timestamp using the values from the table.

The values are :

startmonth = 6
startday = 2
startyear = 2010

Here's what I get --

db2 "select TIMESTAMP(DATE(STARTYEAR-STARTMONTH-STARTDAY), TIME('00.00.00')) from TEST"

Result = 0006-06-26-00.00.00.000000

And of course, if i give this I get the correct answer --

db2 "select TIMESTAMP(DATE('2010-06-02'), TIME('00.00.00')) from TEST"

Result = 2010-06-02-00.00.00.000000

What am I doing wrong in the first query? Thanks!!

n_i · #2 (**permalink**) 09-23-10, 15:42

Quote:

Originally Posted by db2user24

What am I doing wrong in the first query? Thanks!!

You are subtracting values of day and month from the value of year, instead of concatenating their character representations.

db2user24 · #3 (**permalink**) 09-23-10, 16:23

thanks, i tried this.. seems like i still have a syntax error --

db2 "select TIMESTAMP(DATE(CHAR(STARTYEAR) || '-' || CHAR(STARTMONTH) || '-' || CHAR(STARTDAY)), TIME('00.00.00')) from TEST"

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

db2user24 · #4 (**permalink**) 09-23-10, 16:48

never mind.. i got it.. startmonth needs to be = 06 and start day = 02

tonkuma · #5 (**permalink**) 09-23-10, 19:52

Another example using datetime operations of DB2.

Code:

------------------------------ Commands Entered ------------------------------
WITH 
 test(startmonth , startday , startyear) AS (
SELECT 6 , 2, 2010 FROM sysibm.sysdummy1
)
SELECT startmonth , startday , startyear
     , TIMESTAMP( DATE(startday)
                + (startmonth - 1) MONTHs
                + (startyear  - 1) YEARs
                ) AS timestamp
 FROM  test
;
------------------------------------------------------------------------------

STARTMONTH  STARTDAY    STARTYEAR   TIMESTAMP          
----------- ----------- ----------- -------------------
          6           2        2010 2010-06-02-00.00.00

  1 record(s) selected.

db2user24 · #6 (**permalink**) 09-24-10, 01:58

thanks tonkuma!

If i try that, it gives me this...

db2 "SELECT startmonth , startday , startyear , TIMESTAMP( DATE(startday) + (startmonth - 1) MONTHs + (startyear - 1) YEARs ) AS timestamp from test"

SQL0440N No authorized routine named "TIMESTAMP" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

tonkuma · #7 (**permalink**) 09-24-10, 02:32

I guessed that you are not using latest DB2 version/release.

IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1

Quote:

TIMESTAMP

...
If only one argument is specified it must be an expression that returns a value of one of the following built-in data types: a date, a timestamp, or a character string that is not a CLOB. ...

DB2 Version 9.5 for Linux, UNIX, and Windows SQL Reference, Volume 1
(date data type is not included.)

Quote:

TIMESTAMP

...
If only one argument is specified:
– It must be a timestamp, a valid string representation of a timestamp, or a string of length 14 that is not a CLOB, LONG VARCHAR, DBCLOB, or LONG VARGRAPHIC.

If you are using older DB2, please try:
TIMESTAMP( DATE(startday) + (startmonth - 1) MONTHs + (startyear - 1) YEARs , '00:00:00' )

db2user24 · #8 (**permalink**) 09-30-10, 13:10

thanks tonkuma! I tried it out today and it worked!

Lenny77 · #9 (**permalink**) 09-30-10, 14:32

This is your mistake (not exists language where "-" use for concatenate,
for example in SQL Server use "+", in DB2 "||", also values has to be characters. In DB2 "-" uses for substraction, or for changing the sign.

Code:

with test (startmonth, startday, startyear) as
(
select 6, 2, 2010 from sysibm.sysdummy1
)
select STARTYEAR-STARTMONTH-STARTDAY,
TIMESTAMP(DATE(STARTYEAR-STARTMONTH-STARTDAY), 
                 TIME('00.00.00')) from TEST;

Quote:

2002 0006-06-25 00:00:00.000000

where STARTYEAR-STARTMONTH-STARTDAY = 2002
is number of days from day of 0001-01-01 (or 1/1/1)

Next query is the solution, a beat different from tonkuma's solution:

Code:

with test (startmonth, startday, startyear) as
(
select 6, 2, 2010 from sysibm.sysdummy1
)
select 
timestamp('0001-01-01', '00.00.00') 
+ (startyear - 1)  year 
+ (startmonth - 1) month + (startday - 1) day
from TEST;

Quote:

2010-06-02 00:00:00.000000

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech