Add a specific time to a date

[FreeRangeDingo]

I have a Date column that is formatted like this -- 2018-01-08. 

I want to add a specific time to that date so that the column is a DateTime that looks like this -- 2018-01-08 06:00:00. 

I need the time to be 06:00:00. 

I have tried all manner of formula and parsing, but I can't get it to not throw an error.  Here are the last thing two things I tried.

#1 -- 

(Note: in the above the test column is formatted as a DateTime.  I didn't really expect this one to work because I am concatenating a string.  I did also try wrapping DateTimeFormat function around this but it wouldn't work with the time.)

I get an error that "06:00:00 is not a valid DateTime". 

#2 I have also tried taking the output of the test column, changing the data type to string, and then running it to thru the DateTime tool but that gives this error -- ConvError: DateTime (39): DateTime_Out: Cannot convert " 06:00:00" to a date/time with format "%Y-%m-%d %H:%M:%S": Expected separator '-%m-%d %H:%M:%S', got: ':00:00' Record #6178.  I have also tried changing "06" to "6"... but that doesn't work.

Super frustrating.  I know I've done this task before but can't find it in any of my old workflows.  Thanks in advance.

[LukeG · Accepted solution]

@FreeRangeDingo ,

Based off of the error message, it looks like it tried converting only a time to a DateTime.

ConvError: DateTime (39): DateTime_Out: Cannot convert " 06:00:00" to a date/time with format

Are there any records that have an empty value in your [jip start date] column? If so, there will be an error converting " 06:00:00" to a DateTime format.

Let me know if this is helpful. Happy to continue helping if this does not eliminate the error.

-Luke

[fmvizcaino]

Hi @FreeRangeDingo ,

I'm attaching an example showing my calculation. I have tried to reproduce your error but I wasn't able to do it.

Would you be able to share the part of your workflow that this error is happening? That way, I can try to see what is going on.

Best,

Fernando Vizcaino

[FreeRangeDingo]

One record out of 10k had a null jip start date.  I filtered it out and I was able to push the "Test" column to the DateTime tool without error.  Thanks for the help!