datetimeformat makes column null

[ShantanuDagar]

Hello,

I have a column MM/YY with 2023-03-01 as date.

I need only 03 that is month in that.

I am using formula datetimeformat([MM/YY], "%m") to get just the month.

But my whole column turns null after running this.

[BS_THE_ANALYST · Accepted solution]

@ShantanuDagar 
It seems fine for me:

Is the column definitely formatted as a date datatype?

All the best,
BS

[martinding]

Hi @ShantanuDagar,

Are you updating you MMYY column or are you creating a new column?

[mceleavey · Accepted solution]

Hi @ShantanuDagar ,

are you formatting the Month column as a date? If so, this will result in nulls.

If you format the column as a string you should get 03.

M.

[BS_THE_ANALYST]

@ShantanuDagar do you have trailing or leading spaces? If you do, you'll see that the column is formatted as a String datatype. I'd advise using a data cleanse tool to remove the spaces before you apply your formula tool if that's the case.

[martinding · Accepted solution]

Hi @ShantanuDagar ,

Please see attached:

Because your MMYY is a date type, when it gets updated in place, it will become Null, because 03 is not DateTime.

But if you create a new column, and use a string type, it will work.

[Ben_H · Accepted solution]

Hi @ShantanuDagar,

Your column MM/YY is formatted as a date so it doesn't understand the output.

Try creating a new column, it should work then.

Regards,

Ben

[Deano478]

@ShantanuDagar  you can use the DateTimeMonth function like so:

[BS_THE_ANALYST · Accepted solution]

@mceleavey Good spot.

You are trying to extract the month out, but this is not a Date Datatype. Hence, you are getting nulls i.e:

You can resolve this by creating a new column like @mceleavey did above or you can use the Multi-Field Formula tool to apply the formula to the column and simultaneously change the datatype to String. I.e:

All the best,
BS

[ShantanuDagar]

Thanks all for quick solution.

Issue was that only, operating on date column without adding a new column.