Need to re-format a date time field

Hi,

I’m totally new to Business Objects and have been landed with working on it by my company without yet having a training course ! However I have been able to get some good data out of our system but at present I am stuck on one issue :

One field I am working with is ‘Date Time’ which is dd/mm/yyyy hh:mm:ss. I want to run reports which show counts against individual days. Therefore I have been trying to create a new field which is just the date (dd) using format. However this isn’t working and I have the feeling I am down the wrong alley. Can anyone tell me how I can either tell reports to give me data by day or how I can create a new field with only the date within it ?

thanks, Paul.


Mighty_Genghis :uk: (BOB member since 2004-07-09)

Hi, Paul – and welcome to BOB! :mrgreen:

You’ll need to create your object using a function in your database to truncate the time portion off of your date-time column. What database are you using?


Anita Craig :us: (BOB member since 2002-06-17)

If you are using Oracle use

trunc(date_column)

If you are using SQL Server use

cast(convert(char(12),date_column) as datetime)

If you have any questions of this sort, they will probably have already been answered as they are quite common. Please use the SEARCH facility to check for any answers as this saves both on the database size and the number of search results that others have to look through.

And welcome to Bob! :mrgreen:

Anita,

Thanks for your quick reply. We’re using Oracle 9i - is that what you needed to know ?

Paul


Mighty_Genghis :uk: (BOB member since 2004-07-09)

Yes, answer above in my earlier post. Different databases handle dates differently, hence Anita’s question. If you have a spare half hour at lunch, read the Designer and Reporter FAQs on this site as they pre-empt many questions.

Regards,
Mark

thanks both. Will attempt it in the next couple of days.


Mighty_Genghis :uk: (BOB member since 2004-07-09)

So, since you are using Oracle 98, Mark gave you the answer:


Anita Craig :us: (BOB member since 2002-06-17)

Mark,

I’ve tried the truncate command and on the report it then displays only the day. However it is still creating a row for each second - so its displaying correctly but not giving the date correctly (I want daily reports)

Any ideas ?

Paul


Mighty_Genghis :uk: (BOB member since 2004-07-09)

Please, what is the exact SQL code generated by Business Objects for your report?


Andreas :de: (BOB member since 2002-06-20)

I’ve managed to create a Universe object which removes the HH:MM:SS from a field of DD/MM/YYYY HH:MM:SS so that is a great success.

However now I need an object which trims DD/MM/YYYY HH:MM:SS down to DD/MM/YYYY HH - so in effect it takes off minutes and seconds, can anyone help please ??


Mighty_Genghis :uk: (BOB member since 2004-07-09)

This code (Oracle syntax) will truncate a date / time to the hour.

TRUNC(TABLE.DATEFIELD,'HH')

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)