Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Fri October 29, 2021 01:40 PM
    Hello all,

    Cognos version 11.1.6
    Database: SQLSERVER 2016

    What is the correct function in Cognos to use to convert a varchar to a datetime (I need the date and the time)?

    This is what I am doing:
    Taking a date field and a time field, concatenating it together to create a date/24hr time query item (I created one for Start time and one for Stop time)
    CAST(ACTIVITY_DATE AS VARCHAR(18)) || '  ' || SUBSTRING(CAST(replicate('0', 4 - len(START_TIME)) AS VARCHAR(2)) || CAST(START_TIME AS VARCHAR(4)) || ':' || SUBSTRING(CAST(replicate('0', 4 - len(START_TIME)) AS VARCHAR(2)) || CAST(START_TIME AS VARCHAR(4)) || ':00'
    AS StartTime_DateTime
    The result is like 2021-10-29 13:50:00

    Now I need to take the new query item and convert it to a date and time so that I can perform a calculation (StopTime-StartTime)

    Just for clarity:
    The original SQL that runs in SSMS is 
    CONVERT(DATETIME,
    CONCAT(CONVERT(VARCHAR,ACTIVITY_DATE,23),' ',
    SUBSTRING(START_TIME,1,2),':',SUBSTRING(START_TIME,3,2),':00'),120)

    Suggestions?

    ------------------------------
    Towandra Grant
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Fri October 29, 2021 03:41 PM

    cast ( {your string expression or the query item which contains the string expression } , date)      If I recall correctly it also supports datetime.  There are functions such as _days_between (which is documented ) and _hours_between, _minutes_between and _seconds_between (which are not but are supported) which might help you in your interval calculation.

    Here's something which I have, which you can use as a guide.   

    cast (
    substring(
    cast( SHIP_DAY_KEY as varchar(10)) ,1,4 ) || '-'
    ||
    substring(
    cast( SHIP_DAY_KEY as varchar(10)) ,5,2 )|| '-'
    ||
    substring(
    cast( SHIP_DAY_KEY as varchar(10)) ,7,2 )
    , date)



    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 3.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Fri October 29, 2021 03:50 PM

    Here is another example that might be helpful:
    CAST ( CAST ( CAST ( ORDER_DATE, DATE ), VARCHAR ( 20 ) ) + ' 12:23:00', TIMESTAMP )



    ------------------------------
    Kind regards,
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 4.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Mon November 01, 2021 10:08 AM
    Thank you Henk for the response.
    I did use the cast to timestamp function and it did validate and render a correct test sample in the Calculation Definition of the individual query item, but when I tested it within the Query Subject Definition it would validate but gave the following error when trying to do a test sample:
    --XQE-DAT-0001 Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


    ------------------------------
    Towandra Grant
    ------------------------------

    Original Message


  • 5.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Mon November 01, 2021 10:00 AM
    Thank you Ian for your response.  I had tried to cast to date earlier but it did not give me the date and the time....only the date.

    ------------------------------
    Towandra Grant
    ------------------------------

    Original Message


  • 6.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Tue November 02, 2021 03:12 AM
    If it's working in SSMS, you could try using the same calculation instead of relying on Cognos functions.

    CONVERT({DATETIME},
    CONCAT(CONVERT({VARCHAR},[table].[ACTIVITY_DATE],23),' ',
    SUBSTRING([table].[START_TIME],1,2),':',SUBSTRING([table].[START_TIME],3,2),':00'),120)

    Keywords (datetime, varchar) here need to be wrapped in curly braces, but I don't see any reason this wouldn't work.


    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 7.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Tue November 02, 2021 03:49 PM
    Paul,
    I created a new query item in the report and modified my expression to include the curly brackets.  And then I really thought it was going to work when it validated with no errors and I was able to get to the prompt page to chose the date...but then it gave me this error:
    XQE-PLN-0098 The vendor specific function "CONVERT" is not supported.

    ------------------------------
    Towandra Grant
    ------------------------------

    Original Message


  • 8.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Fri November 05, 2021 06:19 AM
    If you pull only that expression into a new query does it work? I suspect that there's something else in the query that's forcing it to do local processing, and once that happens using any vendor functions stop working.

    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 9.  RE: Framework Manager - Convert Varchar to Datetime (SQL SERVER)

    Posted Tue November 02, 2021 08:31 AM

    Cognos Analytics provides the CAST function to convert data types.

    When you intend to CAST from a string type to a temporal type, such as DATE or TIMESTAMP, the character value must follow the SQL standard.

    YYYY-MM-DD

    YYYY-MM-DD HH:MI:SS.fff (up to 9)

    Given your initial description of combining a DATE and TIME value

    CAST(  CAST ( DATECOL, CHAR(11))  || CAST ( TIMECOL, VARCHAR(8)) , TIMESTAMP ) -> converts your character string to a TIMESTAMP type

    • CAST ( DATECOL, CHAR(11)) -> 'YYYY-MM-DD '
    • CAST ( TIMECOL, VARCHAR(8)) -> 'HH:MI:SS'
    •  CAST ( DATECOL, CHAR(11))  || CAST ( TIMECOL, VARCHAR(8)) -> 'YYYY-MM-DD HH:MI:SS'

    Vendors, such as SQL Server offer "similar" functions whereby they allow you to define various string formats, such as CONVERT provides.

    In some cases, can be more straightforward to use those versus nesting substrings, concatenation etc.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

    Similarly, they may also provide a means to define what value should be used if the value cannot be converted.

    cast ( 'HELLO', DATE ) is an error, not a default value of null etc.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


Related Content