What is the correct syntax of "Today-1" object?

[system]

Hello,

I would need to schedule a report on BO 5. For this purpose I would need an object to create in BO designer to reference to it in the query.

Each day I would need to get information from previous day. I plan to limit the date to object with “Today-1” date value. So I created an object:

Name:
Today-1

Select:
Dateadd(dd,-1,Getdate())

But it is not correct as clicking on parse gives “Incorrect syntaxt near From”

Could you please help me to write the correct commands?
Any info is highly appreciated.

Thanks a lot!

Ondrej

---

ovyhnal (BOB member since 2007-08-13)

[system]

Hi,

Here i have attached screenshot for your clarification

Regards,

Tamil
today-1.doc (38.0 KB)

---

Tamil (BOB member since 2007-07-11)

[system]

Hi Ondrej,

all objects based on Getdate() (or for instance Sysdate for Oracle) don’t parse because they do not reference any table.

However, these objects work just fine when you combine them in the query with any other universe objects. Try and see

[Moderator Note: And I am moving the topic from BusinessObjects Classic forum to Semantic Layer when Designer and universe related issues are discussed.]

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks a lot Marek,

I just figured out that I have to create condition (yellow) object with this definition and it works then.

dbo.Jobseeker.DateDeleted* = dateadd(dd,-1,getdate())

That’s the object I need to limit to Today-1

Thanks a lot!
Ondrej

---

ovyhnal (BOB member since 2007-08-13)

[system]

You certainly can create predefined conditions like that, and they should parse. However I believe there is some benefit to having “generic” objects for Yesterday, Today, Start of this Week, and so on. The fact that these objects do not parse should not be a concern.

It is covered in our faq:

FAQ: Designer

How can I use the current system date in an object? You use a different function for each database. For example, Oracle users would typically use sysdate while Sybase or SQL Server users would use getdate(). Teradata uses DATE, and DB2 uses CURRENT_DATE. Ask your DBA if you are unsure which method is appropriate for you. Ok, so I have done that, but my object does not parse. How can I fix it? These functions or pseudo-columns do not need to reference a table. Yet when De…

In our database faq:

FAQ: Designer Database Specific

How do I get the current date as an object in my universe? Oracle - define your object as trunc(sysdate) SQL Server - define your object as cast(convert(char(10),getdate(),23) as datetime) This will remove the time section from your getdate() function - just use getdate() if you want current date and time. DB2 - define your object as one of the following CURRENT DATE CURRENT TIME CURRENT TIMESTAMP DATE(CURRENT TIMESTAMP) See this page for more detailed information …

And in a series of posts on my blog as well. The first post in the series talked about setting up Today and Yesterday, and why you would want to create predefined conditions (as you showed) but also reasons why that might not be a great idea.

---

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

[system]

Thanks a lot Dave! Extemely useful information!!!

Ondrej

---

ovyhnal (BOB member since 2007-08-13)