Date Formatting

[MadeInHB]

I have a workflow that has three columns.  Effect Date (2016-01-05),  First of Current Month (2016-02-01) and 185 Days (2015-07-31).

All of these are in the date format.

I try to format any of the dates so it would be 01/05/2016. 

Here is the formula I have been using. DateTimeFormat([EFFECT DATE],"%m/%d/%y")

No matter what I try, I keep getting NULL.

Any ideas?

[michael_treadwell · Accepted solution]

The function DATETIMEFORMAT() returns a string

In your Formula tool there are two options:

1. Create a new field, something like [Effect Date String] and use the formula DATETIMEFORMAT([Effect Date], "%m/%d/%y") 
2. Or, if you want to update the Effect Date field in the Formula tool, use a Select tool first to convert it into a string

If I had to guess, you are trying to take [Effect Date] which is a Date or DateTime object and update the column with the formula you posted which returns a string and that is causing your NULL field.

I've attached a module

[jdunkerley79]

You might find it easier to use a DateTime tool for this. 

@michael_treadwell solution should work if you prefer a formula tool.

[MadeInHB]

When you format a date, does it always stay as a string?  Is there a way to keep the date in the date format?

[jdunkerley79]

The DateTimeFormat function is for conversion from a date type to a string type. The DateTime tool likewise is for conversion between the two types.

The formula tool can create a new field in the dataset and you can keep the original date as well

[gcamut]

Hello, I have a date in an odd format and using the Custom incoming string option will only allow me to change one date format at a time. 

The date format is dd-Mar-yyy (02-Mar-2016) and I cannot figure out how to specify multiple formats to convert. Any help would be appreciated.

[Ramya1]

Thanks this was useful.

[Mchu6la2lai]

Hello,

I have a similar situation to the original question. I have applied all the steps proposed including using "SELECT" tool to ensure the field is in STRING format, however, I am still getting NULL after using Dateformat function or Datetime tool. I would be most grateful if you could help me.

Original format:

DD/MM/YYYY

I want to convert to

%m-%y

Thanks

Lucy