Hopefully I'm just missing something stupid, but in a SA workbook, is there seriously not a simple way to just do {trandate}>(date)? I've tried YYYY-MM-DD format, MM/DD/YYYY format, enclosing both in single quotes, in double quotes, in no quotes, in pound signs, in nothing, etc. I get a type mismatch every time. This website uses extracts and I got the below to work, but it's a ridiculously complex formula for what it's accomplishing, which is to just do something on a date greater than 6/9/2022. Anyone know a better way?

case when

extract(year from {trandate})>=2022 then (case when extract(month from {trandate})>=6 then (case when (extract(month from {trandate})=6 and extract(day from {trandate})>=9) or (extract(month from {trandate})>6 and extract(day from {trandate})>=1) then 0 else 1 end) else 1 end) else 1 end

Hi! Would this syntax work in your case? Where exactly are you trying to apply this condition?

{trandate}>TO_DATE('2022-06-09')

That worked, thank you! Might have been overthinking it. I believe in saved searches you don't need the to_date part, so I just assume SA workbooks might be the same. Thanks again

Hey @RJMNS! Thank you for bringing this up. Based on your description, it really looks like a true mystery 🙂 So I understand that the only problem is the export which worked for one dataset but didn't work for the other without specifying the format clause. Since there were no errors thrown, how did you recognize that the problem was caused by missing format clause? Or any chance that there is some error in the console of your browser, that would point to this? Please let me know, I am wondering what is going on there ablobthinking

how did you recognize that the problem was caused by missing format clause

A lot of painful trial and error and by process of elimination. I had about a dozen formula fields and so I added them to the data set one by one until I found one that caused it to stop exporting. Then I started swapping out pieces of the formula (multi-level Case statement) until I isolated it to the fact that the export worked with the TO_DATE field removed, but not when it was put back. Noticed in the notes for the TO_DATE field within SA it was supposed to have a format clause like that. So tried adding that in and it exported again. Removed it and the export stopped working. Could be something in the browser I suppose, but the one data set that was working that was missing that clause was run in the same browser. When I first noticed the issue in the other data set, I rebuilt it from scratch, thinking it had gotten corrupted somehow. The problem occurred in the new data set once I added the formula field with the TO_DATE formula. I'm not sure how to isolate which it is (browser or SA), I just know that one works and one didn't using essentially the same formula and the same browser. Super odd.

Got it and much appreciate the time you invested into investigation! In my opinion, it is always better to provide format clause and it is my mistake that I didn't include it in the original reply as if it is not provided, you rely on the default format set on the DB level which I don't know how that looks like for your DB. So if everything works with format provided, let's go with that. The strange behavior you described might have been caused by some caching for example and it will not be easy to figure this out, so unless it is blocking you somehow, I would say we a solution we can go with. But of course let me know if you see any issue with that or if encounter any other, I'll be happy to help.