Problems Casting Date Time to a Date

[hawaii_desperado]

We recently upgraded to Congos 11 from Cognos 10.

In cognos 10 we have Query Items which are created from an oracle DateTime which we used Cast({Query Subject}, Date) and when we do so the "Data Type" changes to Date.

In cognos 11 I started by creating the Query Item without the Cast and it of course set the Data Type to "Date Time".  We didn't want the time displayed and we don't want time required for filtering so I added the Cast.  This changed to display format to Date but the filter still required the time.  I noticed that unlike the Query Items created in Congos 10, the "Date Type" remained Date Time.  But I can't find any way to change it to Date.

So I deleted the Query Item and recreated it this time correctly using the Cast during the initial configuration.  Now the Date Type doesn't populate and there is no way to set it.  In addition when I try to filter or alter the format in Query Studio it says the item can not be filtered on or formatted.  I assume because the Data Type is now unknown.

I tried recreating some of the Query Items I had created in Cognos 10 and found the same results with them so there is not a new issue with my original Query Subject.

Has Congos 11 changed how this should be done?

How does one correctly convert a Date Time into a simple Date so that display and filter treat it as such?

The Query Items carried created in Cognos 10 still use the Cast and have the Data Type changed to Date and they work fine.  But I can not create new Query Items with the same setting no matter how I try because it doesn't allow me to alter the Data Type property and it fails to set it if I use Cast and it hard codes it to Date Time if I do not use Cast. 

UPDATE: Adding more information as suggested.

The expression that works in Cognos 10 but results in NO "Data Type" in Cognos 11 is as follows.

Cast([myGRANT DW].[AWARD_DOCUMENT_ORIGIN_INFO].[ORIGINAL_FINALIZED_DATE],Date)

[myGRANT DW].[AWARD_DOCUMENT_ORIGIN_INFO].[ORIGINAL_FINALIZED_DATE] is the result of a direct database Query Subject which is defined as
Select
    RPT_AWARD_DOCUMENT_ORIGIN_INFO.AWARD_NUMBER,
    RPT_AWARD_DOCUMENT_ORIGIN_INFO.ORIGINAL_FINALIZED_DATE
From
    [DWH].RPT_AWARD_DOCUMENT_ORIGIN_INFO as RPT_AWARD_DOCUMENT_ORIGIN_INFO

ORIGINAL_FINALIZED_DATE is a "DATE" type in Oracle 12 which for oracle always includes the time component.

The result from Cognos 11 when I use the Cast to Date is as follows.  "Data Type" can not be changed.

[cognostechie]

If you expect answers then post the expression you had in ver 10, expression which does NOT work in ver 11 including the results of it and the expression you tried in ver 11 and it's results. Writing a whole lot of sentences saying 'I did this and I did that' is not an effective way to explain your problems.

[hawaii_desperado]

OK thanks for the advice.

The expression that works in Cognos 10 but results in NO "Data Type" in Cognos 11 is as follows.

Cast([myGRANT DW].[AWARD_DOCUMENT_ORIGIN_INFO].[ORIGINAL_FINALIZED_DATE],Date)

[myGRANT DW].[AWARD_DOCUMENT_ORIGIN_INFO].[ORIGINAL_FINALIZED_DATE] is the result of a direct database Query Subject which is defined as
Select
    RPT_AWARD_DOCUMENT_ORIGIN_INFO.AWARD_NUMBER,
    RPT_AWARD_DOCUMENT_ORIGIN_INFO.ORIGINAL_FINALIZED_DATE
From
    [DWH].RPT_AWARD_DOCUMENT_ORIGIN_INFO as RPT_AWARD_DOCUMENT_ORIGIN_INFO

ORIGINAL_FINALIZED_DATE is a "DATE" type in Oracle 12 which for oracle always includes the time component.

The result from Cognos 11 when I use the Cast to Date is as follows.  "Data Type" can not be changed.

[bus_pass_man]

The cast is presumably on a model query subject in FM.

What other factors are in play?  Are you still using CQM or have you switched to DQM?

This changed to display format to Date

Where?

I noticed that unlike the Query Items created in Congos 10, the "Date Type" remained Date Time.  But I can't find any way to change it to Date.

This is presumably in FM. 

Select the query subject and choose tools/update object.

That should update the data type.

[cognostechie]

OK thanks for the advice.

The expression that works in Cognos 10 but results in NO "Data Type" in Cognos 11 is as follows.

Cast([myGRANT DW].[AWARD_DOCUMENT_ORIGIN_INFO].[ORIGINAL_FINALIZED_DATE],Date)

[myGRANT DW].[AWARD_DOCUMENT_ORIGIN_INFO].[ORIGINAL_FINALIZED_DATE] is the result of a direct database Query Subject which is defined as
Select
    RPT_AWARD_DOCUMENT_ORIGIN_INFO.AWARD_NUMBER,
    RPT_AWARD_DOCUMENT_ORIGIN_INFO.ORIGINAL_FINALIZED_DATE
From
    [DWH].RPT_AWARD_DOCUMENT_ORIGIN_INFO as RPT_AWARD_DOCUMENT_ORIGIN_INFO

ORIGINAL_FINALIZED_DATE is a "DATE" type in Oracle 12 which for oracle always includes the time component.

The result from Cognos 11 when I use the Cast to Date is as follows.  "Data Type" can not be changed.

I just tried cast( [Sales].[Order Line].[Date], date) and it works as expected. I shows only the date part, not the time part and our DB is also Oracle which stores time in the field along with the date.

If you notice the 'Data Type' property in your screenshot 'Cognos11_Result.png' you will notice it is blank but the same property in 'Cognos10_Result.png' shows 'Date'. I think this is what the error is pointing to, not to the cast function. Something went wrong during the upgrade and it blanked out the Data Type property. Unfortunately, Cognos will not allow you to change that manually so the best way to fix this will be to delete the Database Query subject and import it again from your version 10 model. Alternatively, you can delete it and create it again and then point the Business Layer query subject to the newly created one. You might want to check the joins of that query subject before deleting because those joins will have to be created again.

Deleting and Importing will be the best because that will bring in the joins too. For importing, Run the metadata wizard, from the 1st screen, choose 'Cognos Model' and then follow the screens to locate your version 10 model.

[hawaii_desperado]

The cast is presumably on a model query subject in FM.

What other factors are in play?  Are you still using CQM or have you switched to DQM?
Where?
This is presumably in FM. 

Select the query subject and choose tools/update object.

That should update the data type.

The Cast is on the Query Item against the Query Subject.

Our model has two layers.  "Database View" which are simple Data Subjects against straight database tables which were denormalized in a data warehouse.  Then we have a "Business View" layer with Query Items grouped in folders.

The Issue is not with the Query Subjects in the Database View layer but rather with the Query Items in the "Business View" layer.

This screenshot should clear things up.


Link to image

[cognostechie]

I just used it and it works perfectly ! Screenshot attached. Read my earlier post also

[cognostechie]

It's strange I cannot insert/attach an image other than from a web URL !!

When I use the cast function, the data type of the query item in the Model Query subject in Business Layer changes from DateTime to Date and it works

[hawaii_desperado]

It's strange I cannot insert/attach an image other than from a web URL !!

When I use the cast function, the data type of the query item in the Model Query subject in Business Layer changes from DateTime to Date and it works

So you are adding the cast to the Data Subject not the Data Item ?
With Cognos 10 we used to cast the Data Item only, the Data Subject type would remain "Date Time" but the Query Item would be cast and the change would work fine.

Perhaps share the image you made via IMGUR as I did or just add it as an attachment like I did in my first reply to you when I had the exact same issues trying to attach an image.

I would have sworn drag and drop used to work on this site but maybe it was taking up too much space in the db so they change it to needing to be a URL reference.  Anyway we digress.....

[cognostechie]

You are probably new to Framework Manager and that's ok. There is nothing called 'Data Subject'. Cast can only be used in a Data Item and there has been no change in that between Cognos 10 to Cognos 11. Database Query Subjects are found in 'Database View' in your model and they have SQLs written in them whereas Model Query Subjects are found in Business View in your model which reference the Database Query subjects. I put cast in a Data Item in Model Query subject.

In my Data Item which has the cast function, the Data Type is Date.

[bus_pass_man]

Did you do  tools/update object?

[hawaii_desperado]

You are probably new to Framework Manager and that's ok. There is nothing called 'Data Subject'. Cast can only be used in a Data Item and there has been no change in that between Cognos 10 to Cognos 11. Database Query Subjects are found in 'Database View' in your model and they have SQLs written in them whereas Model Query Subjects are found in Business View in your model which reference the Database Query subjects. I put cast in a Data Item in Model Query subject.

In my Data Item which has the cast function, the Data Type is Date.

Sorry I called "Query Subject" "Data Subject".  I am not exactly new to Framework Manager but it's not my main job, I am a full stack developer and do reporting infrequently so I have very little experience with it and basically struggle to remember my training from 10 years ago.

Funny you are calling what you changed a "Data Item" but that doesn't exist just like I used "Data Subject".  The terms in my FM are "Query Subject" and "Query Item".  Perhaps you are just using that term because you think I will understand it better.

It seems the Cast creates the Date type for you but doesn't for me.  I can't explain why.  You can clearly see in my screen shot that I used the Cast in my "Query Item" and that it does NOT set the Data Type.  That's the issue I am investigating so it seems we have no progress.

I have discovered that I don't need to do the cast anymore in Cognos 11.  I can just leave it a Date Time and change the default formatting to "Date" and change the Prompt Type to "Date" to get the result I want.  I don't understand why all of the Query Items I created in cognos 10 using the cast do have a Date type and work as they used to and that I can not recreate these using that method on Congos 11 but at least I found a solution that seems to work.

If anyone know why Cast to Date doesn't cause the Query Item to have a data type of Date in my instance of the Cognos 11 framework manager I would love to know the reason.  I am using version 11.2.4.

[hawaii_desperado]

Did you do  tools/update object?

Tools/update object is only available on the Query Subject and not on the Query Item.
I did try it on the Query Subject but of course it remained a Date Time because it is a Date Time from the database and I am not casting it there.

I am using Cast({Query Subject}, Date) when I create my Query Item.

Query Item doesn't have the tools/update object option.

[cognostechie]

Funny you are calling what you changed a "Data Item"

Yes, that's what I meant 'Query Item', sorry.

I have discovered that I don't need to do the cast anymore in Cognos 11.  I can just leave it a Date Time and change the default formatting to "Date" and change the Prompt Type to "Date" to get the result I want. 

It's nice that you figured it out but this feature was also available in earlier versions, even version 8 of Report Studio had it.

I presume your FM Model is working connected to the DB and not working offline.

Try using oracle cast function too, like CAST([Query Item] AS DATE) , CONVERT(DATE,[Query Item]), TO_DATE([Query Item], 'YYYY-MM-DD')

[hawaii_desperado]

Yes, that's what I meant 'Query Item', sorry.

It's nice that you figured it out but this feature was also available in earlier versions, even version 8 of Report Studio had it.

I presume your FM Model is working connected to the DB and not working offline.

Try using oracle cast function too, like CAST([Query Item] AS DATE) , CONVERT(DATE,[Query Item]), TO_DATE([Query Item], 'YYYY-MM-DD')

Unfortunately we are still having an issue with the Query Item because it remains a date time even though it now defaults to display as a date and filter as a date.  So one of my report writers are having issues with writing filters against the Query Item because the time component is messing up the comparisons.   I know I can teach them how to extract the date component and compare that but all of our other date based query items are using the type Date so it's weird that they need to treat only this one differently.

I really need to figure out how to get cognos to recognize this as a date.

You said that in your copy of framework manager using Cast(Query Subject, Date) is correctly setting the data type to Date.   Can you tell me what version of framework manager you are using?   We have 11.2.4 installed but I don't think any of the 4 service pack updates have been applied.  I am wondering if you are on an older version or have the service packs installed.

I am working on getting my old Cognos 10 instance back online so I can compare my results and better explain this issue.  I also need clear examples from my report writer to better understand what they are trying to do.   But honestly it all comes down to a simple issue how can I get the "Date Time" to change to "Date" data type.