Converting nulls on date

I am trying to convert nulls in a date to 2100-12-31.  When SQL Server this would be isnull(datecolumn, '2100-12-31') 

I have tried using ifnull(datecolumn, literal) when creating a formula (where literal is various attempt to of '2100-12-31' in quotes and not in quotes) and thoughtspot says it cant find the literal.  I think I may need to use to_date but I can't see from the example in the documentation how I would do that. 

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Greg Pearson You will be able to create the formula in ThoughtSpot which will accept the date without any quotes (Refer screenshot). Let me know if it works for you (including the version you are on will be helpful).

    Like 1
  • Thanks, I tried that before posting my question with no luck but this time It worked.  Previously I was getting a though spot message saying it couldn't find 12/12/2100.  When thoughspot proposed the closing '(' and I chose it, everything worked fine.  Now I can't get it to fail so I don't have a repeat case for you.

    Like
  • Greg Pearson Glad to know it works now.

    Like
Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 3Replies
  • 385Views
  • 2 Following