Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: Convert string to date

    I have a column which is varchar need to get the output as date.

    Column name - lowval

    '2015-12-31' is the value of the record.

    How to change it to date?

    Thank you in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That depends on the version of DB2 that you're using.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2007
    Posts
    38
    The version is 9.7.5

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I am using something like this:
    SET VAR_DATE = DATE(TIMESTAMP_FORMAT(VAR_FULLDATE_CHAR, 'YYYYMMDD'));
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are making it way more complicated that it is:

    Code:
    db2 "create table date_test (date_id int not null, date_dt date, var_date varchar(20))" 
    db2 "insert into date_test values (1,null, '2015-12-31')"
    db2 "update date_test set date_dt = var_date"
    db2 "select * from date_test"
    
    DATE_ID     DATE_DT    VAR_DATE
    ----------- ---------- --------------------
              1 12/31/2015 2015-12-31
    
      1 record(s) selected.
    Note that the date format displays as MM/DD/YYYY because my client has US Territory Code. But dates are stored internally in a standard format. The display format can be changed with the CHAR function.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2007
    Posts
    38
    Ok, basically I was trying to detach all data partitions from a table(partitioned on date) prior to 180 days. so I was trying to get the data partition name from syscat.datapartitions.

    db2 " select DATAPARTITIONNAME,lowvalue from SYSCAT.DATAPARTITIONS where tabname='TESTTAB' AND DATE(LOWVALUE) < CURRENT DATE - 180 DAYS)"

    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007
    Any help?

  8. #8
    Join Date
    Nov 2007
    Posts
    38
    Never mind got it

    db2 "select date(timestamp_format(substr(lowvalue,2,10) || '-00.00.00','YYYY-MM-DD HH24:MIS')) from SYSCAT.DATAPARTITIONS where tabname='TESTTAB'"

    Thank you all for your help.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If "date(timestamp_format(substr(lowvalue,2,10) || '-00.00.00','YYYY-MM-DD HH24:MI:SS'))" worked,
    why not use "DATE( substr(lowvalue , 2 , 10) )"?

  10. #10
    Join Date
    Nov 2007
    Posts
    38
    Tried that but lowvalue is a varchar column and date function will not work with varchar.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DATE function will work with VARCHAR.

    What query did you tried?
    And, what error message(s) did you got?

  12. #12
    Join Date
    Nov 2007
    Posts
    38
    db2 "select date(substr(lowvalue,2,10)) from syscat.datapartitions"

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

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you added "where tabname='TESTTAB'",
    do you get error?
    db2 "select date(substr(lowvalue,2,10)) from syscat.datapartitions where tabname='TESTTAB'"

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    db2 "select date(substr(lowvalue,2,10)) from syscat.datapartitions where tabname='TESTTAB'"
    I proposed the query.
    Because, you wrote
    Quote Originally Posted by tudaykumar View Post
    Never mind got it

    db2 "select date(timestamp_format(substr(lowvalue,2,10) || '-00.00.00','YYYY-MM-DD HH24:MI:SS')) from SYSCAT.DATAPARTITIONS where tabname='TESTTAB'"

    Thank you all for your help.
    So, if you replaced the expression by "DATE( substr(lowvalue , 2 , 10) )", like...
    db2 "select /*date(timestamp_format(substr(lowvalue,2,10) || '-00.00.00','YYYY-MM-DD HH24:MI:SS'))*/ DATE( substr(lowvalue , 2 , 10) ) from SYSCAT.DATAPARTITIONS where tabname='TESTTAB'"
    it would be
    db2 "select DATE( substr(lowvalue , 2 , 10) ) from SYSCAT.DATAPARTITIONS where tabname='TESTTAB'"

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

    Lightbulb Try again

    Quote Originally Posted by tudaykumar View Post
    I have a column which is varchar need to get the output as date.

    Column name - lowval

    '2015-12-31' is the value of the record.

    How to change it to date?

    Thank you in advance.
    The Date Format is absolutelly right.
    if you'll do
    Code:
    select date(lowval) DT_lowval
    from (select varchar('2015-12-31' ) lowval from sysibm.sysdummy1) a
    you'll get result:

    DT_LOWVAL
    2015-12-31
    why you have error. I think you have error when lowval = ' '.
    It looks like Null but not Null.

    Try to change your select as one of following:

    Code:
    Select Date(NULLIF(lowval, ' '))...
    or, even better:
    Code:
    Select Date(Case when lowval < '0001-01-01' then NULL
                            when lowval > '9999-12-31'  
                            then NULL                                     
                            else lowval
                     End )...
    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
  •