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

Date format changing between variable declaration and usage

Hi,

I'm using variables within my load script to specify a max and min date range to load data for.

I am declaring the following in the variables:

let vFirstDate = date(YearStart(addYears(today(), -2)), 'YYYY-MM-DD');

let vLastDate = date(today()-1, 'YYYY-MM-DD');

When running through debug I can see that vFirstDate is set to 2012-01-01 and vLastDate is 2014-03-16

All good so far.


The next step of the script is to load some data in from the DB, and use these min/max values against a specific date. The line in my script is:

BDT.servicedate between $(vFirstDate) and $(vLastDate)

When the debugger interprets that line however, I get the following SQL:

BDT.servicedate between 2012-01-01 and 16/03/2014

This then fails, as the field on the DB is expected YYYY-MM-DD.

Any ideas?


Thanks!

Rory.

1 Solution

Accepted Solutions
Not applicable
Author

Hi All,


Thanks for the suggestions... turns out it was a spelling error in my script. Total noob mistake.

Does highlight that it would be nice for the script editor to have some intelligence to let you know if you're trying to use a variable that has not previously been declared, but still a silly mistake on my part.

Cheers,

Rory.

View solution in original post

3 Replies
ThornOfCrowns
Specialist II
Specialist II

I normally, for a 'belt and braces' approach, add a CHR(39) to the start and end of the variable, so the line looks like:

between '2012-01-01' and '2014-03-16'

as I find that otherwise it tends to fall over / return no results.

Not applicable
Author

Hey Rory,

Can you try  BDT.servicedate between  '$(vFirstDate)' and '$(vLastDate)'

Not applicable
Author

Hi All,


Thanks for the suggestions... turns out it was a spelling error in my script. Total noob mistake.

Does highlight that it would be nice for the script editor to have some intelligence to let you know if you're trying to use a variable that has not previously been declared, but still a silly mistake on my part.

Cheers,

Rory.