BigSheets DateTime Formatting

Question

I have a CSV that is being read into BigSheets in BigInsights 2.1.2. One of the columns is date in the structure of mm/dd/yyyy. I need to convert this column to the DateTime format. When I click on the column name I have the option to change its data type to DateTime. This gives me a prompt to enter the format and select the timezone. Apparently I'm misunderstanding what the format does, because I enter mm/dd/yyyy and it fails to convert the data properly.

Does anyone know the proper way to use BigSheets and convert the data from mm/dd/yyyy to DateTime? Thank you!

Answer 1

Because the output is in a DateTime format, capitalization is actually important here. By putting mm/dd/yyyy I was in fact telling it that it was to read minute-minute/day-day/year-year-year-year. Months are denoted as capital M's in this format. When fixed to MM/dd/yyyy it was able to properly interpret the input.