BusinessObjects Board

SQL Date Functions in Universe

building semantic layer
#1

Hello Friend I designed my Universe (SQL Data base) with below codes for current month , quarter etc. But it is throwing the below error

Only Current Date is working but rest are not working . Please suggest what need to be corrected , can u tell me if we can remove the time from these date fields or not required

Current Day

Administrator.SUPPORT_INCIDENTS.ENTRY_TIME = GETDATE()

Yesterday

Administrator.SUPPORT_INCIDENTS.ENTRY_TIME = DATEADD(d,-1,GETDATE())

Current Year

Administrator.TRANSACTIONS.EXPECTED_CLOSE between DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

Previous Year

Administrator.TRANSACTIONS.EXPECTED_CLOSE between  DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

Current Month

Administrator.SUPPORT_INCIDENTS.ENTRY_TIME between DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) and DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))

Surpise the way current quarter works in my universe is as below

'Q'||to_char(Administrator.TRANSACTIONS.EXPECTED_CLOSE,'Q')||to_char(Administrator.TRANSACTIONS.EXPECTED_CLOSE,'YYYY')='Q'||to_char(sysdate(),'Q')||to_char(sysdate(),'YYYY')

can u tell what is this and how i can correct the other filters like current month, current week etc

Please help me asap

Thanks
soniya

[Moderator Edit: Added code formatting. Removed unnecessary blank lines - Andreas]

soniyakaren (BOB member since 2009-07-21)

#2

That error appears to be a generic driver error of some sort…

Your syntax is correct for MS SQL…

Can you provide a bit more info about your environment?

Server OS
Server DB Type (Oracle, MS SQL + ver, etc…
Driver Used?

From the error message , I’d guess that the wrong driver is being used for the connection… OR it’s not an MS SQL Database but perhaps MYSQL, or some other variant?

The bottom line appears that the driver you’re using to connect to the database does not support “DateAdd”, I’ve only seen similar messages with an incorrect driver or a non-sql database myself.

JPetlev (BOB member since 2006-11-01)

#3

If you’re using Oracle, this topic is going to be very useful.

I’m guessing it’s Oracle because you’re getting an ORA error returned.

#4

Hi,

What do you mean by ‘my Universe (SQL Data base)’? It is a MS Sql Server database? That’s what one would expect with the function DATEADD, it’s for SQL server.

But your errorcode is from Oracle, ORA-00904.

That’s probably why your currentday works (I didn’t even know Getdate() works in Oracle!) and rest of your objects don’t (which have dateadd).

Please, in future questions, don’t use stuff like ASAP. Just be glad someone is willing to help and make time for your questions.

jobjoris :netherlands: (BOB member since 2004-06-30)

#5

It doesn’t, unless it is created as a user defined function.

pablolee :uk: (BOB member since 2008-07-29)