on 2016 Mar 07 6:27 PM
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
Request clarification before answering.
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:
FCI_DATEDIFF - parameters:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.