Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
  • 1.  convert string to date

    Posted Tue November 21, 2023 06:31 PM
    Hello,
    I'm using the data module to organize some tables and I need to convert a column with string values ​​to date, how can I do this using extract or cast or another function, this is an example of the value I need to convert.

    01 | 2022

    02 | 2022


    ------------------------------
    Andreia Siqueira
    ------------------------------


  • 2.  RE: convert string to date

    Posted Wed November 22, 2023 08:48 AM

    Hello Andreia, 

    Please try this.

    date(
    _make_timestamp (cast(substr( Test_Date,6,4),integer),01,cast(substr( Test_Date,0,2),integer)) 
    )

    I made a dataitem Test_Date with the string value '01 | 2022'



    ------------------------------
    Udaya Rani Palli
    ------------------------------



  • 3.  RE: convert string to date

    Posted Wed November 22, 2023 10:18 AM

    An alternative approach to Udaya's suggestion would be as follows:

    1. Use the 'Split' option within the data module to separate the year and month values from your string. In the source pane, use the ellipsis (...) beside the data item, and choose 'Split'. Mark the pipe character as the delimiter - it should automatically trim the leading and training whitespace from the values. Click Next, and define labels for the new values representing the Month and Year (I called them Mth and Yr).
    2. Add a calculation that converts the Mth and Yr values to integers and assembles them with a dummy day value of 1 into a timestamp. Click the ellipsis alongside the table, and select New > Calculation. Define the expression as _make_timestamp ( cast(Yr, integer), cast( Mth, integer), 1  ) and specify the name you want to use for the new date column.

    This assumes that the values in your original string are the month and year, and that you want the resultant date to be the first of the month for each.

    Cheers!

    MF.



    ------------------------------
    Mark Fry
    Technical Consultant
    ------------------------------