BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

How Do I Trim Off TIME in a Date Object?
3 members found this topic helpful
Goto page 1, 2, 3  Next
 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
David Smith
Principal Member
Principal Member



Joined: 10 Jul 2002

Posts: 136
Location: Toms River, New Jersey


flag
PostPosted: Thu Oct 24, 2002 1:55 pm 
Post subject: How Do I Trim Off TIME in a Date Object?

Group,
I know this is a real simple one, but I'm all twisted around trying to get this thing worked out icon_confused.gif . Here is what I got:

Oracle 8.17
BO 5.14

I have a LOV for a Date Dimension Object in a Universe called FULL_DATE. I need to have this Date Object show 10/24/2002 or 'MM/DD/YYYY' when the user is prompted in a report. However, the prompt LOV shows 10/24/2002 12:00:00AM.

I need to supress the Time stamp part of the date that the LOV brings in from the Oracle Table. I've tried different combinations of TO_CHAR and TO_DATE, but with no luck icon_mad.gif .

If I use TO_CHAR(FULL_DATE,'MM/DD/YYYY') it removed the Time Stamp of the data, but I loose my sort order since it is no longer a Date format.

I know this is simpe, any sample code would be appreciated.
-Dave

_________________
-Dave Smith
Business Objects Developer
Business Objects Certified Professional - BOE
Back to top
reemagupta
Forum Enthusiast
Forum Enthusiast



Joined: 18 Sep 2002

Posts: 1206
Location: Houston, TX



PostPosted: Thu Oct 24, 2002 1:59 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

Use truncate(date_field), it will remove time stamp also keeping teh date as date.

Reema
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Thu Oct 24, 2002 2:01 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

reemagupta wrote:
Use truncate(date_field), it will remove time stamp also keeping teh date as date.

That doesn't affect the LOV display however. Every date field in BusinessObjects displays the time in the LOV. The only way around that is to convert the data into a character string in the format of a date, which generally means you need to set up a custom LOV query. You generally want your database fields to still be dates. icon_wink.gif

Dave

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-08-22 Yes, Virginia, You Can Refresh One Data Provider At A Time
• 2014-07-08 Airlines Could Save Millions in Fuel Costs By Providing Everyone An iPad
• 2014-06-24 Did Florida State Win National Championship By Using Big Data?
Back to top
David Piet
Principal Member
Principal Member



Joined: 01 Oct 2002

Posts: 118
Location: Kansas City (Missouri, of course...)



PostPosted: Thu Oct 24, 2002 2:03 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

David Smith wrote:
Group,
I know this is a real simple one, but I'm all twisted around trying to get this thing worked out icon_confused.gif . Here is what I got:

Oracle 8.17
BO 5.14

I have a LOV for a Date Dimension Object in a Universe called FULL_DATE. I need to have this Date Object show 10/24/2002 or 'MM/DD/YYYY' when the user is prompted in a report. However, the prompt LOV shows 10/24/2002 12:00:00AM.

I need to supress the Time stamp part of the date that the LOV brings in from the Oracle Table. I've tried different combinations of TO_CHAR and TO_DATE, but with no luck icon_mad.gif .

If I use TO_CHAR(FULL_DATE,'MM/DD/YYYY') it removed the Time Stamp of the data, but I loose my sort order since it is no longer a Date format.

I know this is simpe, any sample code would be appreciated.
-Dave


Have you tried datetime-to-date?

_________________
David Piet
Back to top
reemagupta
Forum Enthusiast
Forum Enthusiast



Joined: 18 Sep 2002

Posts: 1206
Location: Houston, TX



PostPosted: Thu Oct 24, 2002 2:17 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

You can again convert the character to date by

to_date(to_char(date_field,'mm-dd-yyyy'),'mm-dd-yyyy')

This should work.

Dave:
Why the truncate fn wouldnot work?

Reema
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Thu Oct 24, 2002 2:22 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

reemagupta wrote:
Dave:
Why the truncate fn wouldnot work?

Try it and see. icon_smile.gif It will remove the time, if there was one, but the LOV values will still show 12:00:00 AM for every date. It's a "feature". icon_lol.gif

Dave
Back to top
reemagupta
Forum Enthusiast
Forum Enthusiast



Joined: 18 Sep 2002

Posts: 1206
Location: Houston, TX



PostPosted: Thu Oct 24, 2002 2:25 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

I cannot try it as I am working on SQL Server 2000 currently.

Thanks
Reema
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Thu Oct 24, 2002 2:30 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

reemagupta wrote:
I cannot try it as I am working on SQL Server 2000 currently.

Same thing should happen. If you have a date object in your universe, pull up the LOV from the query panel, and look at the values. The same think works in Island Resorts if you turn on the LOV for Invoice Date. You have to turn it on in Designer because it is turned off, but you can turn it on to test.

Which brings me to another point, which I should have made earlier, is that date LOV's are usually a waste of time anyway. Unless there is a specific set of dates (month end, for example) then the user is better off just typing in the date value.
Back to top
David Smith
Principal Member
Principal Member



Joined: 10 Jul 2002

Posts: 136
Location: Toms River, New Jersey


flag
PostPosted: Thu Oct 24, 2002 2:41 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

Quote:
Use truncate(date_field),


TRUNCATE = "Removes all rows from a table or cluster" From my SQL book... can you explain what this will do again and how it relates to my TIME and DATE problem?

Like Dave R. Mentioned it is this little extra "Feature" that is buggin me.. actually my users... I guess I could get rid of the LOV....

_________________
-Dave Smith
Business Objects Developer
Business Objects Certified Professional - BOE
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Thu Oct 24, 2002 2:44 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

David Smith wrote:
Like Dave R. Mentioned it is this little extra "Feature" that is buggin me.. actually my users... I guess I could get rid of the LOV....

Truncate works... but at the database level. It will remove the time. The issue isn't a database issue at all, it's a BusObj issue. It just works that way. Every date LOV has a time, no matter what the actual value from the database is. That's why I said you had to make a custom LOV where the date is converted to a character that looks like a date.

Better advice is to remove the LOV for all date objects. icon_wink.gif

Dave
Back to top
digpen
Forum Addict
Forum Addict



Joined: 15 Aug 2002

Posts: 2854
Location: Building the next big thing...


flag
PostPosted: Thu Oct 24, 2002 3:01 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

Dave Rathbun wrote:
David Smith wrote:
Like Dave R. Mentioned it is this little extra "Feature" that is buggin me.. actually my users... I guess I could get rid of the LOV....

Truncate works... but at the database level. It will remove the time. The issue isn't a database issue at all, it's a BusObj issue. It just works that way. Every date LOV has a time, no matter what the actual value from the database is. That's why I said you had to make a custom LOV where the date is converted to a character that looks like a date.


I believe you can still give this to work, but leaving your object as a DATE object, but modifying the LOV SQL to do a to_char(tab.DateObject, 'MM/DD/YYYY')

The LOV will be a character representation, but since you select the "Date" version of the object, it should work fine. If not, you create a "Condition" object like:

Code:

tab.DateObject = to_date(@Prompt('What is your Date?','A','Class\Object',mono,free),'MM/DD/YYYY')


Essentially, turning the results of the character back into a date, but as I said, I don't think it's required. You just need to adjust the SQL that returns the LOV. Don't forget to prevent BO from regenerating the SQL.

-RM
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Thu Oct 24, 2002 3:59 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

Two observations...
digpen wrote:
I believe you can still give this to work, but leaving your object as a DATE object, but modifying the LOV SQL to do a to_char(tab.DateObject, 'MM/DD/YYYY')

If you do this, you'll have to alter the sort as well. To get dates to sort in the proper order in character form you will need to format them as YYYY/MM/DD. Otherwise the sort is wrong.

Quote:
Don't forget to prevent BO from regenerating the SQL.

This is my opinion, but I would consider this a bad practice for universe design. Or report design, for that matter. There is nothing visible on the query that tells you that this option has been checked. And it's a perfect example of what I call the "It worked yesterday" syndrome. icon_smile.gif A universe designer changes something that breaks something else, and has no idea what went wrong. Just my two cents. icon_wink.gif

Dave
Back to top
Andreas
Forum Advocate
Forum Advocate



Joined: 20 Jun 2002

medal_silver.gif*2medal_gold.gif
Posts: 16534
Location: Dreaming of Africa..


flag
PostPosted: Thu Oct 24, 2002 7:00 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

A different opinion:

Why would you have a LOV for dates anyway?
LOV's for dates are of little value to the end-user (unless you have very specific dates you want the end-user to select from).

_________________
Follow me on Twitter
...Reading: .. oops, no book at hand for now.. how unusual for me

Focusing on Data Visualization, SAP BI 4.0, SAP connectivity, Data Modeling, and SAP HANA Certified Associate
Back to top
avaksi
Principal Member
Principal Member



Joined: 22 Aug 2002

Posts: 338
Location: Irvine, CA


flag
PostPosted: Thu Oct 24, 2002 7:09 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

digpen is nearly right but Dave has a point in not hardcoding the SQL. Try if the following helps...

Create a new object to_char(Date, 'yyymmdd') and change your orig object to to_char(Date, 'mm/dd/yyyy'). Then in the LOV of the original object sort it by the new object. That way no hardcoding, u get the dates in order and no timestamp is shown!!

Did that make any sense at all... crazy.gif


Last edited by avaksi on Thu Oct 24, 2002 7:11 pm, edited 1 time in total
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Thu Oct 24, 2002 7:11 pm 
Post subject: Re: How Do I Trim Off TIME in a Date Object?

avaksi wrote:
Create a new object to_char(Date, 'yyymmdd') and change your orig object to to_char(Date, 'mmddyyyy'). Then in the LOV of the original object sort it by the new object. That way no hardcoding, u get the dates in order and no timestamp is shown!!

Did that make any sense at all... crazy.gif

It makes sense, but will also kill any indexes on the date values. And since dates are very often used in conditions... which is the entire point of haing a LOV query afterall icon_lol.gif... then you want to be sure to have an index available for use if there is one.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 3 All times are GMT - 5 Hours
Goto page 1, 2, 3  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0513 seconds using 17 queries. (SQL 0.0025 Parse 0.0358 Other 0.0131)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo