DateTime Parse/ DateTimeFormat returns Not a Valid DateTime

[rahuldale]

Hi All,

I am trying to convert the the string column having dates and time into DateTime format. However I am stuck with the below error:

The string data fields are as below:

And I need the dates to be converted in the format as below: - 

StartDate to be converted as "mm/dd/yyyy hh:mm:ss" (Field Type: DateTime)

EndDate to be converted as "mm/dd/yyyy hh:mm:ss" (Field Type: DateTime)

FromDate to be converted as "mm/dd/yyyy" (Field Type: Date)

ToDate to be converted as "mm/dd/yyyy" (Field Type: Date)

I will be using an Excel file as input source and appending these rows to Oracle table. To accomplish this, I have designed the Alteryx workflow as below:

Steps Performed:

1. Discard unwanted text from StartDate and EndDate column string.

2. Convert string fields(StartDate,EndDate) into DateTime and fields(FromDate, ToDate) into Date format with the use of DateTime Parse tool

3. Use Formula Tool to convert the Date and DateTime fields into the required format.

I am also attaching the above  Alteryx workbook for the reference. 

Any help would be appreciated

Thanks,

Rahul

[JosephSerpis]

Hi @rahuldale the formulas are creating blanks because you are converting a Datetime field into a string with your datatimeformat formulas. The only format alteryx recognises dates as a date format is YYY-MM-DD. By using a datatimeformat you are converting the date to a string so therefore you either need to change your fields to a string beforehand or create new fields that are string data types. In the attached workflow I put a select tool before the formula tool and changed the data type to strings and your formulas now work.

[rahuldale]

Thanks for your quick response @JosephSerpis 

But could you please assist on how can I cast these column fields as dates?

Because I will be configuring Output as Oracle table and inserting this data in existing oracle table where the datatypes for fields (StartDate, EndDate, ToDate, FromDate) is 'Date'. And I don't want these fields to be type string.

Thanks.

Regards,

Rahul

[JosephSerpis · Accepted solution]

Hi @rahuldale those fields will be string fields within Alteryx. If they are in the correct format for Oracle to be recognised as dates and you are inserting to existing table with those columns defined at Date data types then they should remain as date data types in oracle, when you insert the new data. What you need to remember the data types within Alteryx may not necessarily marry up 100% of the time with databases particularity when its around dates. Alteryx only recognises YYYY-MM-DD as a valid date format hence why your fields need to be converted to a string, when you are within Alteryx. However other environments such as Oracle can recognise other date formats. 

[Lizbhernan]

@JosephSerpis I'm having a similar issue but the difference is that when I convert the date it changes the year, month and day to a whole new date. Do you know how I can prevent this?