system
March 11, 2013, 7:24pm
#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)
system
March 11, 2013, 7:27pm
#2
Do you get an error message or the wrong results?
Nick Daniels (BOB member since 2002-08-15)
system
March 11, 2013, 7:50pm
#3
The object will not validate… It indicates that DATEDIFF is not a function
Brigparadise (BOB member since 2011-10-12)
system
March 11, 2013, 8:07pm
#4
Hi,
What database do you use?
Marek Chladny (BOB member since 2003-11-27)
system
March 11, 2013, 8:10pm
#5
SQL Server 2008
Brigparadise (BOB member since 2011-10-12)
system
March 11, 2013, 11:01pm
#6
CURDATE() should be GETDATE() in SQL Server 2008.
BO_Chief (BOB member since 2004-06-06)
MarkP
March 12, 2013, 9:56am
#7
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
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()
system
March 12, 2013, 1:40pm
#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)
system
March 12, 2013, 3:52pm
#9
Hi, please try the datediff with d instead of day
forum_usr (BOB member since 2012-02-15)
MarkP
March 12, 2013, 3:57pm
#10
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
I’m on XI3.1 so don’t know. Is there a function in the object builder when you edit the object?
system
March 12, 2013, 5:16pm
#11
Thanks. I opened a case with SAP. Hopefully they can help
Brigparadise (BOB member since 2011-10-12)
system
March 21, 2016, 9:40pm
#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)
system
March 22, 2016, 8:25am
#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 (BOB member since 2005-03-01)
system
March 22, 2016, 12:12pm
#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)
system
March 22, 2016, 12:39pm
#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 (BOB member since 2005-03-01)
system
March 22, 2016, 1:13pm
#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!?
di652 (BOB member since 2005-02-24)
system
March 22, 2016, 1:48pm
#17
You’ve got 4 elements in your TIMESTAMPDIFF:
The syntax is only 3 - TIMESTAMPDIFF(interval, timestamp1,timestamp2)
Debbie
Debbie (BOB member since 2005-03-01)
system
March 22, 2016, 2:00pm
#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
Thanks again Debbie
di652 (BOB member since 2005-02-24)
system
March 22, 2016, 2:45pm
#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 (BOB member since 2005-03-01)
system
March 22, 2016, 3:27pm
#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)