Teradata handling single digit month and day problem

Question

I have below values coming from a flat file which may contain single digit month & day field:

9/14/2020 07:20:18.630000
7/7/2020 16:24:57.700000
10/24/2019 03:40:52.380000
11/9/2020 20:21:32.420000

Now I need to load this to a column having TIMESTAMP(6) as the data type. Can someone please help on this? I am using TD SQL Assistant version 16.

Answer 1

SQL Assistant is not a load utility, e.g. TPT fully supports dealing with intput like this.

Your other post shows that you already use a RegEx to add the missing zeroes and you apply the correct format. This is indicating bad data in your input. You might try to spot the error in the input file (check how many rows have been loaded and check the following lines).

Or you apply TRYCAST which doesn't fail, but returns a NULL for bad dates. But yikes, it doesn't support FORMAT, thus you must rearrange the MDY to YMD first:

trycast(RegExp_Replace(RegExp_Replace(x,'\b([0-9])\b', '0\1'), '(..).(..).(....)(.*)','\3-\1-\2\4') as timestamp(6))