BusinessObjects Board

How to format a current date to yyyy-mm-dd in universe?

building semantic layer
#1

Hi,
Currently i have a filter in my web intelligence report. This filter will allow me to display all the data within the current month i run the report.
The filter is define in the universe, it is called current_month

So in the filter, i put

substr(sales.sales_date,1,7)=substr(current_date,1,7)

Sales_date in the teradata table is in yyyy-mm-dd format. So the substr function is use to get yyyy-mm.
Let says i run the report today 2009-09-10, so the filter should give me 2009-09 data. When i run the report, the message given is “There is no data corresponding to this query.”
But when i copy the SQL from webi to my teradata SQL assistant, it give me the data that i want!

I doubt the substr(current_date,1,7) is not match with the substr(sales.sales_date,1,7)…Should i format the current_date to yyyy-mm-dd?

Anyone can help?

ylai20 :malaysia: (BOB member since 2008-05-21)

#2 
substr(sales.sales_date,1,7)
substr(current_date,1,7)

Try creating these as objects, what do you see returned in the columns?
What is the data type of Sales Date?

Mak 1 :uk: (BOB member since 2005-01-06)

#3

Hi,

I have try to create an object for current date, but it give me a syntax error. I put substr(current_date,1,7) in the Select field.

Sales date datatype is Date.

ylai20 :malaysia: (BOB member since 2008-05-21)

#4

Hi,

I’m no teradata sql specialist, but I’m pretty sure that when you use substr() the result will be of data type character.
current_date is the date function of teradata sql?
As CurrentDate() is the one of oracle?

So if you create a current date object, create it as just
current_date
and set the type to date.
Does that parse?
( :!: In this case the message should be the query does not reference a table, since you are not referencing a table, any other message indicates a real error)

Try a query with this object and some other object (from a small table) in your universe,
and see visually if it pulls back the current date.

If you create a ‘current month’ version, try
substr(current_date)
and set the type to character.
Does that parse?

Try a query with this object and some other object (from a small table) in your universe,
and see visually if it pulls back the current month as string in the format yyyy-mm.

You should be able to work out where it goes wrong…
May be your sql tool allows some functions or variable references that are not pure sql…

Good luck,
Marianne

marianne :netherlands: (BOB member since 2002-08-20)

#5

Hi,

I put current_date or date also cannot parse. But when i put something like sales_date=current_date-1 is valid!

I just cant create a current date object…anyone know how to create it? Database is teradata.

But i can query current_date As MyDate in teradata!

ylai20 :malaysia: (BOB member since 2008-05-21)

#6

The object is not expected to parse.

How can I use the system date in a prompt or an object? Why won’t they parse?

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

#7

I have created an object, in Select field, i put Date.
When i run in webi, it said ‘The query does not reference a table (Error:WIS 00022)’

I even put current_date, not works…Any idea?

ylai20 :malaysia: (BOB member since 2008-05-21)

#8

Use below object definition:

substr(TO_CHAR(sales.sales_date,1,7)) 
substr(TO_CHAR(current_date,1,7))

Rakesh_K :india: (BOB member since 2007-12-11)

#9

Did you read the FAQ? You must use at least one fully-qualified object (meaning one that references a table) in the same query.

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