Date format question

A2024

Hello all,

I have a snowflake i/p data source. One of a column is a date field and it showing up as floating decimal in Domo. This field has value -3 (even in data source) if it is a null else it is a date..

As this is floating decimal type in domo, under configuration I changed to Date type. When I try to join with another table, I get an error. It says "Failed to parse -3 from this date field..". So looks like it is failing due to -3 values.

Can you help me with the syntax on how to convert this to 0 or null if -3 is found in date field. I believe once I do this prior to join and then join should work.

Appreciate your help.

jimsteph

You may want to wrap your attempted conversion in the IFERROR() function. You'd do something like

IFERROR(DATE(`mydate`), NULL).

If the first argument in the IFERROR() function is successful — in this case DATE(`mydate`) — is successful you get the results of the first argument. If it doesn't, the IFERROR() function catches that and substitutes the second argument, in this case NULL. If you really need error results to be zero you'd just wrap everything in another IF() function and test for NULL.

To verify this worked I created a webform and entered four values in it:

I then created a quick ETL:

The end result is:

GrantSmith

Use a Magic ETL with a formula tile to make the value null if it's -3:

case when `field` <> -3 then `field` end

You can then feed that into an alter column tile to change the type

A2024

Thank @GrantSmith . I tried doing the above. I first reset the type in configuration so it is back to Floating decimal. Then after formula tile I placed Alter tile to convert to Date type.

Now I get an error after the join. The error says "An error occurred in Alter Columns" Column of type Floating Decimal cannot be converted to type Date"

MichelleH

@Aag2023 What do the valid date values look like in Domo? You will likely need to use a STR_TO_DATE() function to parse it into a date field yourself

marcel_luthi

Agree with @MichelleH, first thing is to know what valid values look like in Domo, as there is no intrinsic conversion from Float to Date. Since the type is returned as float, that either means most of your entries were -3 which is how Domo decided to use that type for that field (not so great news and perhaps you'd need to research why -3 is being used in Snowflake for these) or that Snowflake returns the date as a number, perhaps as Epoch, in which case you'll need to convert it using FROM_UNIXTIME() and a base 10 factor.

jimsteph

You may want to wrap your attempted conversion in the IFERROR() function. You'd do something like

IFERROR(DATE(`mydate`), NULL).

If the first argument in the IFERROR() function is successful — in this case DATE(`mydate`) — is successful you get the results of the first argument. If it doesn't, the IFERROR() function catches that and substitutes the second argument, in this case NULL. If you really need error results to be zero you'd just wrap everything in another IF() function and test for NULL.

To verify this worked I created a webform and entered four values in it:

I then created a quick ETL:

The end result is: