BusinessObjects Board

problem with trunc

building semantic layer
#1

hello forum,

I have a problem using the trunc function. To create an object i have used the code

trunc(crt_dt,'mm/yyyy').

Parsing is OK, however, when I test the object in a report I get the error:

any suggestions?

kind regards
Sven

[Moderator Edit: Added code formatting etc. - Andreas]

blackrooster :belgium: (BOB member since 2003-08-25)

#2

Sven,

Did you try running the SQL generated by BO in SQL plus. Does it run successfully there??

Thanks
Reema

reemagupta (BOB member since 2002-09-18)

#3

Have you considered using the TO_CHAR() function to make this conversion? What are you trying to accomplish that requires a Trunc of the date?

Thanks,
Methos

Methos :it: (BOB member since 2003-08-25)

#4

Is it an Oracle date field? If so, try using the to_char function

to_char(crt_dt,'MM')

[Moderator Edit: Added code formatting - Andreas]

Karen Burton :us: (BOB member since 2002-08-22)

#5

Thank you all,

your suggestions worked. I also can use this to create a measure that sums something for the month to date as in:

sum(case when to_char(crt_dt,'MM/YYYY')=to_char(sysdate,'MM/YYYY') then 1 else 0 end)

However, what do I do when I want to this for the previous month? This was why I was playing around with the trunc funtion …

thnx
Sven

blackrooster :belgium: (BOB member since 2003-08-25)

#6

Here is what you can do for previous month

sum
(
   case when to_char(crt_dt,'MM/YYYY')=to_char(trunc (sysdate, 'MONTH')-1,'MM/YYYY') 
     then 1 
     else 0 
   end
)

Where trunc (sysdate, ‘MONTH’)-1 gives the last day of the previous month

[Moderator Edit: Added code formatting - Andreas]

reemagupta (BOB member since 2002-09-18)

#7

Try the following:

sum(case when to_char(crt_dt,'YYYYMM') = to_char(sysdate,'YYYYMM') - 1 then 1 else 0 end)

[Moderator Edit: Added code formatting - Andreas]

Karen Burton :us: (BOB member since 2002-08-22)

#8

Thank you reemagupta and Karen,

nice when you learn something! Therefore, Karen, could you elaborate why your solution works?

thanx
Sven

blackrooster :belgium: (BOB member since 2003-08-25)

#9

Be aware that Karen’s solution works for the most part, but watch your January dates. Since the Year/Month combination becomes text, subtracting 1 from a 01 month will return 00. So…: 200301 becomes 200300.

Whenever possible, you will want to retain your dates as date fields until the last possible second (before converting). It would be better to convert your date into another date, subtract the appropriate days and then turn to a string:

select to_char( to_date( to_char(to_date('31-DEC-2003'),'YYYYMM')||'01','YYYYMMDD') -1, 'YYYYMM') from dual;

This takes my string and turns it to a date… in this case using the YYYYMM portion and then adding a 01 to make it the first of the month. I then subtract one day (bringing me into November of 2003). Once I’m in November, I reformat it to a string.

This contains extra TO_DATES, but you should be able to break this down.

-RM

[Moderator Edit: Added code formatting - Andreas]

digpen :us: (BOB member since 2002-08-15)

#10

This will work except for January. In January of 2003, the sysdate expression would resolve to 200300, which is not valid. I would suggest using the add_months() function instead, as in:

sum(case when to_char(crt_dt,'YYYYMM')=to_char(add_months(sysdate, -1),'YYYYMM') then 1 else 0 end)

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#11

Using the trunc function to truncate an object date field, the list of values still show the timestamp.

12/13/2013 12:00:00 AM
11/27/2013 12:00:00 AM
11/26/2013 12:00:00 AM

Trunc(AUDT_ESTA_STUS.LST_UPD_DT)

Is this an error in BO?

When below SQL run in TOAD:

select trunc(LST_UPD_DT)
from AUDT_ESTA_STUS

Results are correct
12/13/2013
11/27/2013

racerchaser (BOB member since 2013-12-13)

#12

What’s the type/format of your Object? If it’s a date Object, I’d think it would still include the timestamp if your format is set to display the timestamp.

ChrisW1204 :us: (BOB member since 2011-04-21)

#13

Date objects always have time stamp associated to them. You can, however, create a Custom List of Values to displays the values as character strings without the time stamp.

Select to_char( table.col, ‘mm/dd/yyyy’)
Order by trunc( table.col )

This should display a string for the LOV without changing how the object is actually handled.

digpen :us: (BOB member since 2002-08-15)

#14

It’s a very old topic. :slight_smile: Also one that is discussed in the FAQ:

Dave Rathbun :us: (BOB member since 2002-06-06)