Cast field as date

[BigChris]

Hi,

I'm not sure if this is possible, but I'll ask anyway. I've got a field that's a DateTime field. I can change the format of the field so that when it's in a report or query it doesn't show the time element...so far so good. What i'd like to do is have a version of the field with the time element removed. So, I tried this:

Code Select Expand

cast([DateTime Field], date)
which on the face of it works fine. However, on closer examination, the resulting field has an nVarChar format in Framework Manager...and I can't seem to change that.

Has anyone else done anything similar? And if you have, were you able to have the result as a date field?

In case it's relevant I'm running over SQL server.

Many thanks,
C

[MFGF]

Hi,

I'm not sure if this is possible, but I'll ask anyway. I've got a field that's a DateTime field. I can change the format of the field so that when it's in a report or query it doesn't show the time element...so far so good. What i'd like to do is have a version of the field with the time element removed. So, I tried this:

Code Select Expand

cast([DateTime Field], date)
which on the face of it works fine. However, on closer examination, the resulting field has an nVarChar format in Framework Manager...and I can't seem to change that.

Has anyone else done anything similar? And if you have, were you able to have the result as a date field?

In case it's relevant I'm running over SQL server.

Many thanks,
C

Hmmmm. Interesting. I just tried the exact same thing, and the resultant calculated item doesn't show any data type for me. When I test it, I see just the date part of the original date time item - exactly as I'd expect. I'm using Cognos 10.2.2 over SQL Server 2012.

Did you add this in a model query subject or a data source query subject? I did mine in a model query subject...

Cheers!

MF.

[BigChris]

If this gets too rambling, my apologies in advance...but I thought it might be worth giving the full tale.

My original reason for checking this out is that the queries and reports based on this table seemed overly complicated when you looked at the SQL. The SQL seemed to be doubling back on itself with an outer join. I tracked it down to the same datetime field that had been cast as a date in the middle (modelling) layer of our framework.

I took that cast out of that level and the resulting queries reverted to what I would have expected...a single pass of the table. I then thought I'd create a cast version of the datetime field so that users can select a day without worrying about the time element. I put the calculation in at the Enterprise layer, and that's where I'm seeing the nVarChar date type. I tried changing the format to see if that had any effect, but it didn't. The data type field is greyed when I click into it, so I can't change it all. The plus side is that at the Enterprise layer the query retains the single pass.

I'm using Cognos 10.2.0 and Sql Server 2008 r2.

[MFGF]

If this gets too rambling, my apologies in advance...but I thought it might be worth giving the full tale.

My original reason for checking this out is that the queries and reports based on this table seemed overly complicated when you looked at the SQL. The SQL seemed to be doubling back on itself with an outer join. I tracked it down to the same datetime field that had been cast as a date in the middle (modelling) layer of our framework.

I took that cast out of that level and the resulting queries reverted to what I would have expected...a single pass of the table. I then thought I'd create a cast version of the datetime field so that users can select a day without worrying about the time element. I put the calculation in at the Enterprise layer, and that's where I'm seeing the nVarChar date type. I tried changing the format to see if that had any effect, but it didn't. The data type field is greyed when I click into it, so I can't change it all. The plus side is that at the Enterprise layer the query retains the single pass.

I'm using Cognos 10.2.0 and Sql Server 2008 r2.

What do you mean by the Enterprise layer, Chris? Is that your data source query subjects?