Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Add a specific time to a date

8 - Asteroid

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 -- 

starter bits.png

add together.png

(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.


string to date tije.png


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



@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.



16 - Nebula
16 - Nebula

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.



Fernando Vizcaino

8 - Asteroid

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!