Using date variables
Hi,
I'm wanting to store a date in the project variable list, but can't figure out how to make the variable read as a date in, say, a formula step in a recipe.
I've tried to format the date as "yyyy-MM-dd" with and without quotes, as well as "yyyy-MM-ddT00.00.00.000Z" with and without quotes.
When I do the simple date format without quotes (i.e., just 2022-09-24) the variable UI kicks me the following error message
"Invalid format: Expected ',' or '}' after property value in JSON at position 70"
When I put the simple date in quotes (i.e. "2022-09-24") then the variable UI accepts it, but when I try to use the variable in a formula, it simply resolves the math of the string, in this case to "1989". Here are two views of that - one just calling the variable, another trying to use the variable in a date formula (asDate):
I feel like there must be a way to define the variable as a date, but I can't find that in the documentation.
Thanks for any assistance!
Operating system used: MacOS Monterey v12.4
Operating system used: MacOS Monterey v12.4
Best Answer
-
Miguel Angel Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 118 DataikerOptionsHi,
DSS stores variables as strings. Yet, we can later on parse them to the format we desire.
For example, in this case the 'asDate' formula fails because the format provided in the second argument does not correspond to the fed data. For example. see Capture.PNG attached (where Rdate is 1989)
On the other hand, we can also use a 'Parse date' processor to transform the variable into a date, though in this case it is a two step process as the processor acts on columns. See Capture2.PNG
Answers
-
thanks! This helped. It would be great if there was a way to define a variable as a date, bu managing it through parsing as you described works, so I'll move on.
-
What if the first thing you are doing is running a query, and you won't have your date column/values until after your query runs?
Dataiku will not let you parse the date before running the query. Trying to do this with a variable/app scenario, but coming across this issue. -
How would this extend to reading in SQL data. If I connect a SQL table and use a query.
-
Hello,
if you want to declare in your Global Variables in Date format, you need to add extra simple quotes in you global variable decalration. For example :
{ "Initial" : "'2024-01-01'"}and then when you call it in the formula DSS : you simple do this asDate(${Initial}, "yyyy-MM-dd")
Please make it as solved if so thanks :) -
I did something similar except the extra simple quote in the variable wasn't an option because I called the variable in the Dashboard as well. So my solution was to add the simple quotes in the SQL recipe (this worked for Oracle Server, didn't try for other SQL servers).
to_date('${start_date}','mm/dd/yyyy')
