Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Add quotes around variable BeginOfYear
EDIT: Use Date
Add quotes around variable BeginOfYear
EDIT: Use Date
Thank you, this worked.
Can you explain why I need to use Date, rather than Date#?
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')