Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Framework Manager - How to convert datetime to date (sqlserver database)

    Posted Fri January 22, 2021 04:13 PM
    What is the correct sql to use in Framework manager to convert a datetime query item to date only?
    I tried using convert({date}, [Database View].[trans_action].[posting_date], 102) in the Calculation definition of the query item.
    When validating and testing within the calculation definition, it works and returns a date (with no time) in the Test Results window.
    However, when I try to validate it in the Query Subject Definition I get the following error:
    XQE-PLN-0098 The vendor specific function "convert" is not supported.

    What is the equivalent of the convert function that will work within Framework Manager?

    ------------------------------------
    Cognos Analytics 11.1.6
    Sql Server
    Framework Manager

    #CognosAnalyticswithWatson


  • 2.  RE: Framework Manager - How to convert datetime to date (sqlserver database)

    Posted Fri January 22, 2021 06:52 PM
    Did you try cast(field ; date) ?

    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------

    Original Message


  • 3.  RE: Framework Manager - How to convert datetime to date (sqlserver database)

    Posted Mon January 25, 2021 07:34 AM
    cast function did not work in the oracle. But it works with to_char(DateTime,'mm-dd-yyyy')  and it automatically converts to the character.

    If it converts to date here, how does it work with the date prompt?. ( I believe, date prompt is a DateTime datatype)

    Thanks,
    Ram

    ------------------------------
    Ramanujam Rajagopal
    ------------------------------

    Original Message


  • 4.  RE: Framework Manager - How to convert datetime to date (sqlserver database)

    Posted Mon January 25, 2021 08:57 AM
    Thank you Jeam.

    Either of these two syntax works perfectly for a sqlserver datetime to date only field conversion:

    cast(field;date) or cast (field as date)

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

    Original Message


  • 5.  RE: Framework Manager - How to convert datetime to date (sqlserver database)

    Posted Tue January 26, 2021 10:49 PM

    Cognos implements the ISO-SQL CAST function which can be used to convert several source to target data type combinations

    CAST ( x, DATE ) where x is a timestamp will convert the value to a DATE, effectively removing any time value x held.

    Keep in mind, some vendors SQL DDL may have the keyword DATE, but in reality their implementation is a timestamp (i.e. ORACLE).

    As applicable, the expression will be generated in a SQL statement using the vendor's implementation of CAST or their equivalent.



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

    Original Message