Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Create timestamp using month, day and year from table

    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!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post


    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.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    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
    Last edited by db2user24; 09-23-10 at 17:45.

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    never mind.. i got it.. startmonth needs to be = 06 and start day = 02

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that you are not using latest DB2 version/release.

    IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1
    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.)
    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' )

  8. #8
    Join Date
    Nov 2007
    Posts
    265
    thanks tonkuma! I tried it out today and it worked!

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink one mistake, only

    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;
    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;
    2010-06-02 00:00:00.000000
    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •