topic How to use calculated value in SQL statement in QlikView

How to use calculated value in SQL statement

Thu, 13 Nov 2014 12:22:33 GMT

Hi,

I want to fetch data from a database starting from the beginning of the current year. For this, I stored the following the following outcome of a function in a variable:

LET BeginOfYear = Date#(YearStart(Today()), 'D-M-Y');

My query is the following:

SQL SELECT *
FROM GTISMVREPL.dbo."JOBCALL_ABNAMRO" WHERE JOBCALL_JOB_DATE > $(BeginOfYear);

However, when reloading, I discovered that the data fetched is far more than needed.

When I build up the query where I manually enter the date like this:

SQL SELECT *
FROM GTISMVREPL.dbo."JOBCALL_ABNAMRO" WHERE JOBCALL_JOB_DATE > '1-1-2014';

The right amount of data is fetched.

How can I calculate the beginning of the current year and use that in my query/queries?

Re: How to use calculated value in SQL statement

anbu1984 — Thu, 13 Nov 2014 12:30:56 GMT

Add quotes around variable BeginOfYear

SQL SELECT *
FROM GTISMVREPL.dbo."JOBCALL_ABNAMRO" WHERE JOBCALL_JOB_DATE > '$(BeginOfYear)';

EDIT: Use Date

LET BeginOfYear = Date(YearStart(Today()), 'D-M-Y');

Re: How to use calculated value in SQL statement

Thu, 13 Nov 2014 13:00:08 GMT

Thank you, this worked.
Can you explain why I need to use Date, rather than Date#?

Re: How to use calculated value in SQL statement

anbu1984 — Thu, 13 Nov 2014 13:05:51 GMT

Date() - Convert Date to String

Date#() - Convert String to Date

Result of YearStart(Today()) is Date. If you want to change the format of date, then you have to use Date()

If your input is string and you want to convert to date, then use Date#() -- Date#('1-1-2014', 'D-M-Y')