Sysdate Causing Issues

[system]

Hello All,
Recently while working with dates.I have encountered with a situation.I have created a table in the database which returns sysdate.That’s the only field I have in that table.I created an universe based on that table.When i checked the data (Properties–> Displayed) .I found two dates instead of one.And the second value is not appropiate a date. I have no clue why this behavior is.

I am using BOXIR3.1 SP3.
Database: Oracle 10g.

I have checked in the database and it returns one value.
I have attached a few screen shots to understand the situation better.

Also, the object query:

SELECT DISTINCT
DT.D
FROM
DT

Thank you,
Subrato.

---

subratomall (BOB member since 2009-02-25)

[MarkP]

Why are you creating it this way?

Refer to the Relative Dates sticky topic at the top of this forum section on how to create objects involving sysdate

[system]

Hi Mark,
Thank you for your response and my apologies for the delay in response.

I have checked the Relative date post in the forum. And I have already tried those SQL in universe designer by creating objects like “Yesterday”, “Last month” etc. And also tried to pull them into the report. Since, sysdate do not refer to any table. It throws error some time (especially if the universe has buit for ad-hoc reporting).

Our actual plan is to create a function and call all these dates functionality from it. But to avoid the dependency on DB team I thought of trying something else.

But I think it will not work in that way in BO. Still, I am not able to find why BO cannot handle this? Why it is not understanding the “sysdate” value returing by a database table like any other table ?

Thank you,
Subrato.

---

subratomall (BOB member since 2009-02-25)

[MarkP]

When you say it throws an error, it will do if you don’t include any table based objects. Why would you return just “Yesterday” in a query?

[system]

Associate sysdate universe object to any other table in the universe. This will sort out ur issue.

---

andagunda (BOB member since 2012-11-08)

[MarkP]

@andagunda

Associate sysdate universe object to any other table in the universe. This will sort out ur issue.

Why bother? sysdate is a system function that doesn’t need a table. You’re creating unnecessary overhead in every query

[system]

"Why would you return just “Yesterday” in a query? "

Well Mark I am not.But some time unknowingly user do that.And they don’t like getting any error.I know their dislike is not genuine. .

I will ask my question to you in a different manner.

I have a table in the database(let say:unknowingly some one creates it) which returns “sysdate” only.I have been told to create 5 different variables name as:( Current_Business Day,Previous_Business Day,Rolling 12 Weeks from current date,Previous_Quarter,Previous_Month) by using [b] sysdate[\b] column in BO.

In BO we do not have direct functionality to get these date.I will be going with a standard approach like sysdate,(sysdate-1),…etc as universe object.

Do you know any other approach to get these date variable in BO/Universe?

---

subratomall (BOB member since 2009-02-25)

[system]

Hi,

That’s the correct approach.

This sticky topic can help you:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

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

[system]

Do you know any other approach to get these date variable in BO/Universe?

Yes, create a proper calendar table…

---

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

[system]

“create a proper calendar table”

I didn’t get you :? .Could you please elaborate?

Thank you,
Subrato.

---

subratomall (BOB member since 2009-02-25)

[system]

Hi Subrato,

The Calender Table must be common to all the domains. In Dataware Housing terms you can call it as “Confirmed Dimension”.

Say for example this table will have the common dates for all depts and you can use this table acroos all the Universes, wherever you need to refer the Common DATE’s.

Date : Daily : Weekly : Monthly : Yearly : Holiday
26/03/13 Y--------N----------N---------N--------N
27/03/13 Y--------N----------N---------N--------N
28/03/13 Y--------N----------N---------N--------N
29/03/13 Y--------N----------N---------N--------Y (Good Friday)
30/03/13 Y--------N----------N---------N--------N
31/03/13 Y--------Y----------Y---------N--------N

Use the flag (Y/N) to pull out the respective date.

@Mak 1 - Please correct me, if i am wrong

---

yourajai (BOB member since 2011-03-09)

[system]

There are many ways to do this One other way is to use a Julian date as a reference point and comparing this to today. You can then calculate the number of days, weeks, months e.t.c. I outlined this approach here:-

https://bobj-board.org/t/139560

---

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