- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Hi there,
I am trying create a query that will retrieve all the Invoices Items with ChargeDate on the current month with Export ZOQL, through an ETL tool.
So far I am able to filter the Invoice Items with ChargeDate within the last 30 days but I would like to narrow the search to the ones within the current month. I found a page (https://knowledgecenter.zuora.com/DC_Developers/SOAP_API/M_Zuora_Object_Query_Language/Export_ZOQL/G...) where explains the MONTH() function:
returns only the numeric value of the month from a timestamp. Example: MONTH(CreatedDate) returns 2 if the CreatedDate timestamp value in a record was 2011-02-01T08:52:13.087
However with the following query, I get the error 90007.
SELECT ……
FROM InvoiceItem
WHERE …… AND InvoiceItem.ChargeDate >= 'today - 1 month'
AND InvoiceItem.ChargeDate < 'today'
AND MONTH(InvoiceItem.ChargeDate) = MONTH('today')
Am I not using correctly the MONTH() function?
Thanks,
eclf
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Hi @eclf,
Currently, there is no replacement for month() function yet. You would have to pass physical dates into the filters (e.g., InvoiceItem.ChargeDate >= '10/01/2016' AND InvoiceItem.ChargeDate < '11/01/2016'). As a workaround, depend on what application/programming language is being used, you can try to pull the dates automatically based on Today Date using available tool/method that your application offers.
If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Export ZOQL - Month() function
Hi @eclf,
The correct syntax is
MONTH(InvoiceItem.ChargeDate) = '11'
However, I think this function has been deprecated so it may not work anymore.
If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Export ZOQL - Month() function
Hi @vu_phan,
Thanks for your response. I do not want to hardcode the month because this will be a report that runs every month, so the month needs to be picked up dynamically.
I just need to figure out how to get the Month of the date today (as in the month of the day that the report runs).
If you say this MONTH() function has been deprecated, is there any other function I can use for this purpose? A replacement or an improved version?
Thanks a million!
eclf
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Hi @eclf,
Currently, there is no replacement for month() function yet. You would have to pass physical dates into the filters (e.g., InvoiceItem.ChargeDate >= '10/01/2016' AND InvoiceItem.ChargeDate < '11/01/2016'). As a workaround, depend on what application/programming language is being used, you can try to pull the dates automatically based on Today Date using available tool/method that your application offers.
If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Export ZOQL - Month() function
Hi @vu_phan,
Thanks for your response. Instead of physical dates we are passing relative ones (like today - 7) and just adjust the scheduling so "today" lands in on the day that we want.
Anyways, thanks for the tips.
Best,
EF