Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

How to use calculated value in SQL statement

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?

Tags (5)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: How to use calculated value in SQL statement

Add quotes around variable BeginOfYear

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


EDIT: Use Date

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

View solution in original post

3 Replies
anbu1984
Honored Contributor III

Re: How to use calculated value in SQL statement

Add quotes around variable BeginOfYear

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


EDIT: Use Date

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

View solution in original post

Not applicable

Re: How to use calculated value in SQL statement

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

anbu1984
Honored Contributor III

Re: How to use calculated value in SQL statement

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