How can I convert parameter input date to YYYYWW format and use it in SQLStatement section in XML publisher?

Question

I have created an xml file as data definition template for my xml publisher. The data template has two parameters which are date( startdate,enddate). But this input dates should be converted to YYYYWW format so that in my SQLstatement I can use the start date and end date to check if the given date(in YYYYWW) format is between the startdate and enddate.

this is the parameters in my data template xml :

<parameters>
        <parameter name="P_org_id" datatype="number"/>
        <parameter name="P_Account_Number" dataType="number"/>
        <parameter name="P_Start_week" dataType="date"/>
        <parameter name="P_End_week" dataType="date"/>

</parameters>

this is the sql statement section where I have to use the parameter values

select x,y,......z
from apps.XXONT_M545_CUST_COVG_DATA cd
where
cust_org =:P_org_id
and revision_week between :P_Start_week and :P_End_week
and account_number = NVL(:P_Account_Number, account_number) 
and hold_yn = 'Y'

Answer 1

Since you are using NVL(), I'm assuming this is OracleSQL.

A date can be translated to a formatted string with TO_CHAR(date, format)

... 
and revision_week between TO_CHAR(:P_Start_week, 'YYYYWW') and TO_CHAR(:P_End_week, 'YYYYWW') 
...