Data type in framework manager model.

[mrmo]

I have created a view in SSMS 2008R2 and defined one of the columns as a date, this is the week beginning.

I have then created a package in framework manager, that has defined the data type as nVarChar(10)

I have then created a report in report studio

The idea was basically that I could take todays date and compare to the column in the package and get everything that has happened this week.

However it doesn't seem to be working. It doesn't seem to be seeing a date just characters? and casting as date doesn't appear to be working.

First thought is there a way to force FWM to format the column as a date??

Then try again?

[nav_shiv]

Hi
When you say the report is showing characters, can you give an example please?  The original date in the database and how it looks in the Report.

Also is the column week beginning a date or nVarChar type in the source table.

[mrmo]

in the source table it is a date,

In the final report I have two items one is a date time in the source database the other is the date.

The date time is still a date time in FWM
and if I use the format tools in report studio it can be affected.
The date, is in Year, Month, day format and anything I do in report studio to change the format has no affect.

If it matters I am in the UK and am aware that when the system was installed some things are in EN(US) and some in EN(GB)

[bdbits]

In your original post, you said it was nvarchar in FM, and the last post that it is "still a date time". So which one is it in Framework Manager? Did FM appear to choose the wrong data type in the query subject? If FM thinks your column is a string, what is the expression in the view for that column? Is it actually a string when seen through the view? You need to have FM to see it as a date if you want to use it as such in reports.

[mrmo]

To try and be clear,

I may have confused by mentioning the second date related field, I was just trying to give context to what I was trying to achieve if someone could suggest another method.

In the database I have a field of type date, the view that I have on that still treats the field as a date, the FWM model is built on the view and has assigned nVarChar as the property of the date, format is yyyy-mm-dd

There is a second field that starts as date time and FWM assigns it date time. this one includes a time as well as the date.

[bdbits]

If FM believes it is nvarchar, the SQL for your view is probably outputting a string however much it may look like a date. If you go into SSMS and expand the database, then views, then your view, then columns, you can see the datatypes. What does it show for this column?

Until FM agrees that it is a date, Cognos is going to see it as a generic string. This is why formatting datetime is having no affect - it needs to be a date first.