How do I reference a date value column in an if statement formula (e.g. if(date<"2018-01-01","true","false")?

Solved!
dominic_bardele
Level 2
How do I reference a date value column in an if statement formula (e.g. if(date<"2018-01-01","true","false")?
Hello, I am trying to write a formula (if statement) that references a date column. I've tried many various combinations of referencing the date value with and without the val() command and with various formatting of the actual date column, but I cannot get my if statement to work. My goal is to write something like if(date_column<2018-01-01,"TRUEoutput","FALSEoutput"). Nothing shows up in the sample output or the actual output so I must be writing my expression wrong. Any ideas? My date format is shows as "MM/dd/yyyyT00:00:00.000Z".
0 Kudos
1 Solution
Alex_Combessie
Dataiker Alumni

Hello,



At the moment we do not support > or < operations in formulas for dates. Instead, you can use a formula like this:



if(diff(date_column.asDate("MM/dd/yyyy"), '2018-01-01'.asDate('yyyy-MM-dd'), 'days') < 0, 1, 0)



If your date column has the 'T' in its values, the format for the asDate() parameter will be "MM/dd/yyyy'T'HH:mm:ss.SSSZ



Cheers,



Alexandre

View solution in original post

3 Replies
Alex_Combessie
Dataiker Alumni

Hello,



At the moment we do not support > or < operations in formulas for dates. Instead, you can use a formula like this:



if(diff(date_column.asDate("MM/dd/yyyy"), '2018-01-01'.asDate('yyyy-MM-dd'), 'days') < 0, 1, 0)



If your date column has the 'T' in its values, the format for the asDate() parameter will be "MM/dd/yyyy'T'HH:mm:ss.SSSZ



Cheers,



Alexandre

dominic_bardele
Level 2
Author
Thank you!!
0 Kudos
MehdiH
Dataiker

Hi @dominic_bardele 

It is now possible to use comparison operators for dates:

if(date_column.asDate("MM/dd/yyyy") < '2018-01-01'.asDate('yyyy-MM-dd'), 1, 0)

Or if date_column is already correctly parsed using the "Parse date" step:

if(date_column_parsed.asDate() < '2018-01-01'.asDate('yyyy-MM-dd'), 1, 0)

Cheers

Mehdi

Labels

?
Labels (1)
A banner prompting to get Dataiku