Removing timestamp from date field - when exporting report

[ry1633]

In Framework Manager is there a way to truncate the timestamp off of a database field that has a date/time in it?   - like this:  8/22/2014 9:59:17 PM    (MM/DD/YYYY HH:MM:SS)

Specifically I need the truncation to remain in place, when a user exports a report outside of Query Studio or Report Studio into Excel for example.  I've been able to truncate off the timestamp by changing the Date format of the field - which works fine for whenever the user stays in Cognos.  But when they export to Excel, the timestamp returns.

[Lynn]

I generally model the same field twice so the user can choose to report on the date alone or the date with the timestamp.

The data format property isn't really removing the timestamp...it is just hiding it from view.

You could try using the cast function to cast your date/time to a date. This actually truncates (aka removes) the time portion from the data item.

Code Select Expand


cast ( [YourDateTimeField], date )


I'm not really sure about SQL Server though because I vaguely remember that it only has a date/time data type. Not sure if I'm correct about that and it's been a long time since I've had to suffer through anything to do with SQL Server so may be a non-issue.

[ry1633]

I tried that - seemed to work OK.  Have to run it by my users and see how they like and also how they want.

Interestingly,  I tried it both ways just now in a project I'm working on.  I changed the Date data type in its Properties to 'Date' and I also created the calculated field.     When I exported out of Query Studio to Excel 2007/2010 - both fields had the timestamp removed though I had done it two different ways.  Strange.

[ry1633]

Another interesting thing I wasn't aware of.   

When I just change the Date format in Properties in Framework Manager .. then in Query Studio when I run it as 'View in Excel 2007 Format' – I don't get the timestamp.  But when I run it with 'View in Excel 2007 Data Format" – the timestamp is still there  Very interesting.   Why is that?   Maybe I need to take it up with the QS folks.

[bdbits]

The data format property isn't really removing the timestamp...it is just hiding it from view.

Another way of saying what Lynn said... When you export it to "Data" format, you are essentially getting the raw data behind your report.

This is not a bug, it is a feature.  ;D

[ry1633]

Just point of clarification here.   I have some date fields in views in my query subjects that have the timestamp truncated off at the view level (by the SQL statement itself the way my dba wrote the view), and also by the Date formatting in Framework Manager.   And yet the timestamp re-appears when I export to Excel Raw Data mode in Query Studio.     I assume that this is expected behavior and there is no workaround?

[bdbits]

If the data type is datetime and no time portion is provided it is set to midnight (00:00:00) - that is a datatype/database thing. Nothing Cognos-specific about that at all.

Formatting data is not the same as changing the datatype, e.g. via a cast or convert in SQL. Formatting only changes the way the data appears. If you choose a format that shows time on a datetime datatype, you will see the time portion even if it is 00:00:00.

None of this is Cognos-specific really. I would expect this from all tools that know how to handle a datetime datatype.

[ry1633]

I've found something interesting related to this.  My dba and I have been experimenting with ways around the timestamp issue; and he's tried converting dates at the view level to a character string which seems to work OK and removes the time stamp.   BUT...if I try to use the CAST statement on the incoming date field when it's formatted as a character string, then Cognos balks and gives me an error.  I guess that's to be expected in terms of data type handling, but I was wondering (hoping?) that it still might be possible to CAST a date into a DATE type, when it comes in as a character string?

[bdbits]

A cast should work fine. What is the error? What does your expression look like?