Db2

 View Only

  • 1.  Date query

    Posted Fri December 20, 2019 09:46 AM
    Hi Team,

    I have a DB2 view and in which one field datatype is string (format: yyyymmdd) and I want to change it to date data type. 

    Assume field name is abcd. I researched some posts on web, but dint get answer.

    please help as I m beginner to DB2

    Regards
    Sundeep

    ------------------------------
    sundeep kotaru
    ------------------------------

    #Db2


  • 2.  RE: Date query

    Posted Fri December 20, 2019 01:11 PM
    Sundeep,  I think you have a couple of paths to do this.  One is to add a new column to the table and convert the content of abcd to a DB2 date format and update your new column.  When complete you can rename the abcd to abcd_old and rename your new column to be abcd.
    Option 2 may be to unload the table in such a way that you construct your new date format as you unload the table, have the DBAs re-create the table  with abcd changing from a string to a DATE type column then you can load the table from you unloaded data.  One thing you MUST ensure no matter which option you choose is that if you change this column that every application that uses it can handle the new format.  If you can't control that it might be in your best interest to add a column to this table that is a date type column and then run a process to convert abcd into your new abcd_date column and leave both on this table. I hope this helps in some way and welcome to the world of DB2.

    ------------------------------
    Robin Sulsona
    ------------------------------

    Original Message


  • 3.  RE: Date query

    Posted Mon December 23, 2019 09:21 AM
    date(substr(abcd,1,4)||'-'||substr(abcd,5,2)||'-'||substr(abcd,7,2))

    https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000784.html
    Note that the hyphens say that the date string is in ISO format.  Other formats are USA with slashes - mm/dd/yyyy - and EUR with dots - dd.mm.yy

    ------------------------------
    JAMES CAMPBELL
    ------------------------------

    Original Message


  • 4.  RE: Date query

    Posted Mon December 23, 2019 09:21 AM
    date(substr(abcd,1,4)||'-'||substr(abcd,5,2)||'-'||substr(abcd,7,2))
    Note that the dashes say the date string is in ISO format - yyyy-mm-dd .  Other date formats as USA with slashes - mm/dd/yyyy - and EUR with dots - dd.mm.yyyy.

    ------------------------------
    JAMES CAMPBELL
    ------------------------------

    Original Message


  • 5.  RE: Date query

    IBM Champion
    Posted Fri January 17, 2020 06:20 AM
    Depending on the version you are running on you could easily use the TO_DATE function.
    SELECT TO_DATE(abcd,'YYYYMMDD') FROM YOUR_VIEW;

    https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0007109.html

    ------------------------------
    Sebastian Zok
    ------------------------------

    Original Message