Month function in sql query where clause

[Anonymous]

Hi!

What is wrong in my where clause:

I have a ODBC connection and I'm trying to limit the data by year and month functions in the query.

This is my where limit and the year seems to work but it gives error with the month "invalid tag".

FROM IFSAPP."GEN_LED_VOUCHER_ROW"
WHERE YEAR(VOUCHER_DATE)=$(vYear) and MONTH(VOUCHER_DATE)=$(vMonth);

The variables are

And the form of voucher date in the database is

What am I doing wrong?

Regards,

Heidi

[Kushal_Chawda]

try with single quotes

WHERE YEAR(VOUCHER_DATE)='$(vYear)' and MONTH(VOUCHER_DATE)='$(vMonth)';

[tamilarasu]

Hi Heidi,

Change your variable like below,

Let vMonthStart = Date(Monthstart(today()),'D.M.YYYY h:mm:ss');

Let vMonthEnd = Date(Monthend(today()),'D.M.YYYY h:mm:ss');

And SQL statement like below

FROM IFSAPP."GEN_LED_VOUCHER_ROW"

WHERE VOUCHER_DATE> '$(vMonthStart)' and VOUCHER_DATE='$(vMonthEnd)';

[avinashelite]

Problem is the formats are Different i.e

WHERE YEAR(VOUCHER_DATE)=$(vYear) and NUM(MONTH(VOUCHER_DATE))=$(vMonth);

MONTH() will return a text string and your comparing with the num

[Kushal_Chawda]

or try this where instead of creating variables

WHERE VOUCHER_DATE BETWEEN trunc (sysdate,'mm') AND SYSDATE

[Anonymous]

FROM IFSAPP."GEN_LED_VOUCHER_ROW"
WHERE YEAR(VOUCHER_DATE)='$(vYear)' and MONTH(VOUCHER_DATE)='$(vMonth)';

Before reloading it is better debug your query so that u can know which values are getting populated into $(vYear) and $(vMonth)

[tamilarasu]

Sorry. I have wrongly mentioned as = instead of <=. Correct statement should be

FROM IFSAPP."GEN_LED_VOUCHER_ROW"

WHERE VOUCHER_DATE>= '$(vMonthStart)' and VOUCHER_DATE<='$(vMonthEnd)';

[Anonymous]

Thanks all for help!

I ended up doing a bit like Tamil adviced, using the month start and end dates to limit my data into a spesific month with between function.