Date format Changes

rachhh

Hi @GrantSmith ,

This is the date format I have in the dataset :

I wanted to remove the C and have it in text to alter it as Date :

Thus I used this formula on the formula tile (RIGHT(Date, 10)) and got it in :

However I tried using the Alter tile and Formula tile (STR_TO_DATE(Date, '%Y-%m-%d')) which did not work, giving me this error message below :

Appreciate your kind help!

MarkSnodgrass

Based on the error message, it looks as though one of your rows contains a value of LAST_RUN and not any form of a date. You can try and handle this two different ways. In your formula tile, you can try wrapping a TRY_CAST function around your STR_TO_DATE function like this:

TRY_CAST(STR_TO_DATE(Date, '%Y-%m-%d') as DATE)

This will attempt to convert it to a date field and if it can't it will just make the value null.

In the alter tile, click on the settings cog next to the cast data type and select Null in the Bad Values dropdown list. This will do the same thing as above, as it will make the value null if it can't convert it to a date for you.

MarkSnodgrass

Based on the error message, it looks as though one of your rows contains a value of LAST_RUN and not any form of a date. You can try and handle this two different ways. In your formula tile, you can try wrapping a TRY_CAST function around your STR_TO_DATE function like this:

TRY_CAST(STR_TO_DATE(Date, '%Y-%m-%d') as DATE)

This will attempt to convert it to a date field and if it can't it will just make the value null.

In the alter tile, click on the settings cog next to the cast data type and select Null in the Bad Values dropdown list. This will do the same thing as above, as it will make the value null if it can't convert it to a date for you.