Alteryx Designer Discussions

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

Add a specific time to a date

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

 

3 REPLIES 3
LukeG
Alteryx
Alteryx

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

 

Best,

Fernando Vizcaino

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

Labels