Timestamp diff () in IDT

[former_member195433]

  Hello experts ,

I need to find difference in years between  the current  time stamp and  REF_DT time stamp . I  figured to use  now() function to get current time stamp .

The is the expression i tried to get the year

timestampDiff (6, REF_DT,now() ) 

I am not sure why  the numeric 6 in the first argument  . In fact i  am not sure  how to define 1st numeric argument .

Any help  on how to use timestamp Diff ()

usage timestamp DIff ( Expression : NUmeric ,First argument:Date Time , Second argument  DateTime)

[former_member182521 · Accepted solution]

Hello Promod,

The value specifies the interval that is used to determine the difference between two timestamps.

Based on your underlying datasource we can refer the below

For DB2 refer this

1 - Microseconds
2 - Seconds
4 - Minutes
8 - Hours
16 - Days
32 - Weeks
64 - Months
128 - Quarters
256 - Years

For SQL Server refer here

http://msdn.microsoft.com/en-US/library/ms189794(v=sql.90).aspx

For Oracle refer here

http://docs.oracle.com/html/A95915_01/sqfunc.htm#i1006893

Regards,

Mani

[former_member195433 · Accepted solution]

Mani ,

Thank you so much . But i checked the connection and its coming from data federator. How do i know  about the data source or lets say if i have tables from both sql and oracle what syntax do i need to follow .

FYI

In my case  numeric 6 is giving  the difference in months .

and i checked the results numeric 8 is giving in years .

Thanks ,

pramod

[former_member182521 · Accepted solution]

Have you tried with Data Federation Administration Tool to get the individual data source

[former_member195433 · Accepted solution]

Mani,

I found out tables are from  oracle  and i think the database functions  which i see in IDT are related to Data federator  specific  .

[former_member182521 · Accepted solution]

Interesting Pramod. Can you post your finding and close this thread as well so that it could help someone who is looking for similar type of information. Thanks!

[former_member195433 · Accepted solution]

Mani ,

I  am guessing my database functions in IDT are different  and  they are specific to data federator .

Bcoz from they link you gave the expressions for oracle looks like the following and I have a

numeric 6 ,  which is fetching me months .

<interval>

specifies the unit of the second operand, <value_exp1>. The following keywords are valid values for intervals: SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_WEEK SQL_TSI_MONTH SQL_TSI_QUARTER SQL_TSI_YEAR

These are functions i see in my tool . Would you say that   the list of fucntions depends upon the type of connection used   ?  or  these fuctions are generic for  all type of  data sources ( oracle , sql or SAP BW )