Business Analytics

Expand all | Collapse all

Date Time Field Notes

  • 1.  Date Time Field Notes

    Posted Mon October 26, 2020 12:27 PM
    Edited by Dave Chirayath Mon October 26, 2020 02:49 PM

    I'm adding a new Query Item in Framework Manager. I am working in CA 11.1.4.

    I want this field to be only the date portion of a date-time database field.

    The field expression is cast([Audit].[COGIPF_USERLOGON].[TIME STAMP], date)

    When I test the data it appears that the field is only returning the date portion, but the Properties of the field still show the data type to be Date Time and when imported into Cognos, it still has the time portion. (though the time portion is all 0).

    I have tried the following:

    1. Updating the Usage to Attribute
    2. Updating the Formatting so that the Date Style is Short
    3. Modifying the underlying database view to contain a field that is doing the casting, then reference this field directly in the Query Subject.

     The Query Item is still being treated as a Date Time in all of these cases.

    Screenshots attached.



    ------------------------------
    Dave Chirayath
    ------------------------------


  • 2.  RE: Date Time Field Notes

    Posted Mon October 26, 2020 04:48 PM
      |   view attached
    Dave,

    I've encountered this too when creating a date-only version of a dttm field in FM.  It has never stopped us from using that field as a date-only field however.  As long as it is cast as a date - be in it in FM or in the query/report itself - it works like a date regardless of the fact that properties of the field still refer to dttm.  See example for us attached.



    ------------------------------
    Logan Whitaker
    ------------------------------

    Attachment(s)

    pdf
    CAST DATE.pdf   151 KB 1 version


  • 3.  RE: Date Time Field Notes

    Posted Tue October 27, 2020 10:00 AM
    Edited by Dave Chirayath Tue October 27, 2020 10:01 AM
    Thanks, Logan. Unfortunately, the behavior that is described is not what I'm seeing. If I look at the data in FM, it does not have the time component, but in Cognos it does.
    See the attached pic.


    ------------------------------
    Dave Chirayath
    ------------------------------



  • 4.  RE: Date Time Field Notes

    Posted Tue October 27, 2020 02:34 PM
    Edited by Logan Whitaker Tue October 27, 2020 02:53 PM
      |   view attached
    Hi Dave,

    Your screenshot shows the date field still manifesting as a dttm field in a data module.  So when you use a Cognos function of CAST(<FIELD>,DATE) it may still manifest as a dttm with the appending midnight timestamp in some areas of the application - in your case a data module.  It sounds like you don't want Cognos recognizing it as a dttm at all/ever.  So another approach may to create an expression in the first [database] layer in FM upstream of any pre-final/final layers you may have to model the data/relationships before exposing fields to the user.  You can edit the expression in the first layer using the command your database recognizes as changing a field type.  Most of my sources are Oracle DBs so the syntax is very similar to that of Cognos:  CAST(<FIELD> AS DATE).  I did this and tested it in a data module and it worked for me.  See attached steps I took.  

    ------------------------------
    Logan Whitaker
    ------------------------------

    Attachment(s)

    pdf
    CAST AS DATE.pdf   428 KB 1 version


  • 5.  RE: Date Time Field Notes

    Posted Wed October 28, 2020 09:02 AM
    Thanks for your help with this Logan - much appreciated!

    ------------------------------
    Dave Chirayath
    ------------------------------