Usage of FCI_DATE functions

[Former Member]

Hi,

I am trying to write a custom Query Defintion which fetches all master agreements expiring in the current month. There are standard ones which fetch MAs expiring between a date range.. but I want to give the date range as first day of current month to last day of current month.. I came across many date functions like FCI_DATEDIFF, FCI_DATETRUNC etc but not able to figure out the usage..

For example, found this in a standard query:

<%SCHEMA%>.FCI_DATEDIFF(<%TS%>, T2.EXPIRATION_DATE_DATE, 16) ,

Here what is <%TS%> and what is the significance of number 16? Please help.

Also would be great if someone can list out all the date functions available to sourcing Query Definitions

Thanks,

Gayathri

[former_member190023 · Accepted solution]

Hi Gayathri,

It's a bit difficult to get and detail all available functions into one document... mainly because they are scattered in a bunch of the app's jars.

For a quick check the easiest way would be to just connect SqlDeveloper or Toad to a Sourcing database and get the info there.

Regarding your question about FCI_DATEDIFF:

• <%TS%> is actually a Sourcing token, which gets translated to the 'current timestamp'. If you're using Oracle, just use SYSTIMESTAMP or SYSDATE. I imagine the token was created to cope with the multi-database usage of Sourcing (DB2, Max) but someone forgot to document it.

FCI_DATEDIFF - parameters:

• STARTDATE -> DATE
• ENDDATE -> DATE
• DATEPART -> NUMBER

DATEPART can take following values and will return the difference between the dates in:

2     -> seconds

4     -> minutes

8     -> hours

16   -> days

64   -> months

256 -> years (PS: if you want to use this, you need to TRUNC() the call to the function, the developers forgot to do it in the result)

... If you are using Oracle as DB, then this function is mostly pointless. Input parameters are of type DATE (which holds no TIME), so getting seconds, minutes, hours is nonsense.

Regards,

Bogdan

[Former Member · Accepted solution]

Thank you Bogdan for the prompt reply! Unfortunately I dont have access to the DB so cant connect and try out the functions And we are on DB2.  Do you know if we have any FCI_DATE function that will fetch the first day and last day of current month? If I give say March 8, 2016, I want to get March 1, 2016 and March 31, 2016.. any easy way of doing this?

[Former Member · Accepted solution]

Currently I am using this logic (specific to DB2, got it by searching and trying out DB2 syntax) for fetching MAs expiring in the current month:

AND T1.EXPIRATION_DATE_DATE BETWEEN

(<%TODAY%> - (DAY(<%TODAY%>)-1) DAYS)

AND

((<%TODAY%> + 1 MONTHS) - DAY(<%TODAY%>) DAYS )

Is there a generic function that I can use instead to get the first day and last day of current month?

[former_member190023 · Accepted solution]

Hi Gayathri,

Send me an email on tomabogdan@gmail.com and I can send you the list of functions I can find.

Meanwhile, for your requirement I do remember a function in DB2 called LAST_DAY(date), for getting the last day of the month.

For getting the first day of the month you can use: FCI_DATETRUNC2(date, 64)

Bogdan

[former_member190023 · Accepted solution]

Alternatively, instead of using 'BETWEEN', you can try to apply a format on both dates and use equal...

Something in the lines of

format(EXPIRATION_DATE, "MM-YYYY") = format(TODAY, "MM-YYYY)

Bogdan