Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Add a specific time to a date

FreeRangeDingo
11 - Bolide
11 - Bolide

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 Alumni (Retired)

@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
17 - Castor
17 - Castor

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

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