Need DateTime and String in Same Column

[LIEZL]

Hi Everyone,

Hoping this awesome community can help with this issue. I tried different suggestions I've found, but still haven't been able to find the right solution.

We have multiple tables which we are unioning, however, 1 table has a time field with values such as "00:01:38" which is causing the error. (I know that having mixed data types in a column is not common practice, however, the business is requiring these values to be in the same column.)

I have tried parsing out the values into 3 columns, formatting these columns as string and then concatenating them back together. All works fine... UNTIL I add the ":" back in. 😑 This is where it breaks at the Union.

Sample of expected results. Issue with "Time of Call" when Unioning

How I parsed time to string values

Number and % value work fine which perplexes me since the "%" is a character such as the ":"

Any advice on how to get around this?

Thank you!

[Qiu]

@LIEZL 

Can you enlighten us a bit more about the problem here.
better with an input and desired output.

When you say "which is causing the error.", can you elborate what is the error in what situation?

[LIEZL]

@Qiu 

Thank you for your reply!

After creating the table and testing in SQL with the unions, this is the error message I get:

"Error converting data type varchar to float."  -- I believe this is because of the format of "TimeofCall".

When I don't include the ":" it unions perfectly. But I need to include them somehow. 

Thank you.

[Qiu]

@LIEZL 
Maybe you can include a CAST and CONVERT function in SQL Query to convert the data type from fload to varchar before union?

[danilang]

Hi @LIEZL 

The error message that you're receiving comes from SQL Server.  The only type of column in SQL that will accept all the values that you're passing in is a string type char, varchar, etc.  

Dan

[LIEZL]

Thank you @Qui... I think it might have to come down to that. But my source is from an Excel file so I'll see how I can do that.

We are creating tables from these Excel sources and then Unioning them. I've suggested to the business that we can parse it out so it reads "3min 30 secs" but they are not keen to that idea. I really appreciate your time.

[LIEZL]

Hi @danilang ,

Yes that is the error from SQL as I was testing the tables after they were created to see if the union would work after trying to fix the field type in Alterxy. 

[LIEZL]

Hi everyone,

I've found a resolution with the business where the value will be converted to seconds. I found this formula online to use and this works when I try to union this data with the existing data.

DateTimeHour([Actual]) * 3600 + DateTimeMinutes([Actual]) * 60 + DateTimeSeconds([Actual])

Thank you again for your time! Cheers!