BusinessObjects Board

DATEDIFF

building semantic layer
#1

Hi,

I am trying to use the datediff function with curdate and it is not working My syntax is:

DATEDIFF(DAY,@catalog('GUONMFGTST')."GUONMFGTST.PROD"."CONTAINER_DETAIL"."DATE_OF_MFG",CURDATE())

Any help would begreatly appreciated
Thanks

[Moderator Edit: Added code formatting - Andreas]

Brigparadise (BOB member since 2011-10-12)

#2

Do you get an error message or the wrong results?

Nick Daniels :uk: (BOB member since 2002-08-15)

#3

The object will not validate… It indicates that DATEDIFF is not a function

Brigparadise (BOB member since 2011-10-12)

#4

Hi,

What database do you use?

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

#5

SQL Server 2008

Brigparadise (BOB member since 2011-10-12)

#6

CURDATE() should be GETDATE() in SQL Server 2008.

BO_Chief :us: (BOB member since 2004-06-06)

#7

Break it down and start with your object as just:

@catalog('GUONMFGTST')."GUONMFGTST.PROD"."CONTAINER_DETAIL"."DATE_OF_MFG"

If that works then use:

datediff(day,@catalog('GUONMFGTST')."GUONMFGTST.PROD"."CONTAINER_DETAIL"."DATE_OF_MFG",getdate())

Also consider if you want days between to calculate based on current date and time or just current date. If it’s current date, you’d need to strip the time off getdate() by using cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) instead of just getdate()

#8

Hi,

I tried your syntax:

datediff(day,@catalog(‘GUONMFGTST’).“GUONMFGTST.PROD”.“CONTAINER_DETAIL”.“DATE_OF_MFG”,getdate())

I received this error when I try to validate:

Error:
[Data Federator Driver] [Server] The column name ‘day’ could not be found in any of the tables or subqueries of the FROM clause

Cause of Error
The column name ‘day’ could not be found in any of the tables or subqueries of the FROM clause

Since IDT is using the Data Federator Driver, where can I get a list of all the functions supported with IDT.

Any help would be greatly appreciated

Thanks

Brigparadise (BOB member since 2011-10-12)

#9

Hi, please try the datediff with d instead of day

forum_usr (BOB member since 2012-02-15)

#10

I’m on XI3.1 so don’t know. Is there a function in the object builder when you edit the object?

#11

Thanks. I opened a case with SAP. Hopefully they can help

Brigparadise (BOB member since 2011-10-12)

#12

Were you able to resolve this issue?

I am running into the same error message when I validate my IDT Universe… Function 'DATEDIFF is unknown

I am currently running 4.x

di652 (BOB member since 2005-02-24)

#13

Is it a multisource universe? IDT gets very picky with its syntax if you’re trying to use oracle and SQL server data sources together.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#14

Yes, it is a multisource universe.

That said, I have no idea what the syntax or the function to use; why do they make things soooo hard!?

di652 (BOB member since 2005-02-24)

#15

Have a look at my post here (3rd post down): DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

There are some relative dates that work in multisource IDT. You may be able to pick something out from that?

These were based on code I found at http://www.smallsql.de/doc/sql-functions/date-time/index.html - and a lot of time, effort, coffee and swearing…

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#16

Hi Debbie,
Okay that shows me the syntax and tried it via:

SUM(CASE WHEN timestampadd(‘SQL_TSI_QUARTER’, timestampdiff(‘SQL_TSI_QUARTER’, 0, @Select(Revenue Report\Business Date), 0) = timestampadd(‘SQL_TSI_QUARTER’, 0, timestampdiff(‘SQL_TSI_QUARTER’, 0,curDate()), 0)) Then @Select(Revenue Report\Qty Sold) else 0 END)

But now I am getting "Unsupported signature Timestampdif(INTEGER, INTEGER, DATE, INTEGER)

I really don’t think these new functions work like the dateadd and datediff of SQL Server which should have worked nicely in returning me the qty sold for the quarter(in this case).

I feel blinded on learning a new SQL code and syntax; and I am on a deadline… yesterday!? :cry:

di652 (BOB member since 2005-02-24)

#17

You’ve got 4 elements in your TIMESTAMPDIFF:

The syntax is only 3 - TIMESTAMPDIFF(interval, timestamp1,timestamp2)

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#18

There is no second date to work with.
I was using the 0 as a way to get QTD as I would when I used dateadd and datediff within other UNV files I worked in and worked like a charm.

But I will take your advice and see if I can figure it out hoping it doesn’t take me long :frowning:

Thanks again Debbie :slight_smile:

di652 (BOB member since 2005-02-24)

#19

I just had a play in an old test UNX I had (I tried an oracle/SQL server multisource universe a while back and it was so slow, I gave up and did it with ETL instead …)

timestampdiff('SQL_TSI_DAY',@Select(Completed Date/Time),@Select(Validated Date/Time))

This validates perfectly, relating two date objects in my universe. But as soon as I replace either date with a zero, I get the same ‘Unsupported signature’ error you did.

And I’m sure I’ve used zero in the past the same way you have - in complex date calculations in SQL Server. Clearly IDT doesn’t like them, so I suspect you’ll need to find a date of some sort to put in there instead!

Debbie[/quote]

Debbie :uk: (BOB member since 2005-03-01)

#20

Thank you thank you thank you for confirming this!

I feel better that someone besides me got this stupid message.

Okay, so I don’t have a second date to work with so somehow I need to find or get one.

Thanks again Debbie

di652 (BOB member since 2005-02-24)