Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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
Master III
Master III

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'); 
Not applicable
Author

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

anbu1984
Master III
Master III

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