Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

korheidi
Not applicable

Month function in sql query where clause

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

7 Replies
kush141087
Not applicable

Re: Month function in sql query where clause

try with single quotes

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

tamilarasu
Not applicable

Re: Month function in sql query where clause

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
Not applicable

Re: Month function in sql query where clause

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

kush141087
Not applicable

Re: Month function in sql query where clause

or try this where instead of creating variables

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

rgvavihs
Not applicable

Re: Month function in sql query where clause

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
Not applicable

Re: Month function in sql query where clause

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)';

korheidi
Not applicable

Re: Month function in sql query where clause

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.