Export ZOQL - Month() function

[eclf]

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

[vu_phan]

Hi @eclf,

The correct syntax is 

MONTH(InvoiceItem.ChargeDate) = '11'

However, I think this function has been deprecated so it may not work anymore.

[eclf]

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

[vu_phan · Accepted solution]

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.

[eclf]

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