Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Date format change

Hi all ,
I have one isue with date format , In my source file we are getting date format like below
20170610000000
20170611000000
20170612000000

In my staging table we have made the column datatype as time stamp, while loading process are getting failed because of this
is there any way to change the date format while copying from file to staging table
I tried to change the value of date format properties of s3 load component from
"auto" to YYYYMMDD , but this also not worked for me

please suggest the best way to achieve the solution
Thanks in advance ,
Regards,
Vipin Jha

7 Community Answers

Matillion Agent  

Laura Malins —

Hi Vipinkumar

If you change the date format to YYYYMMDDHHMISS it will load.

Thanks
Laura


Vipinkumar Jha —

Hi I used the date format which you suggested, but getting same error


Vipinkumar Jha —

This is what I have configured currently
Date Format = 'YYYYMMDDHHMISS'
Time Format = auto


Matillion Agent  

Kalyan Arangam —

Hi Vipin,

I managed to load into a timestamp using the following -

DateFormat – auto
TimeFormat – YYYYMMDDHHMISS

Here’s my table definition for your reference -

CREATE TABLE “public”.“testdata” (“col1” timestamp) ;

Hope this works for you.

Best
Kalyan


Vipinkumar Jha —

Hi, Thanks.
With this setting(DateFormat - auto ;TimeFormat - YYYYMMDDHHMISS )
It worked for the one of the Date column having data in : YYYYMMDDHHMISS format

But, there is another date columns too in customer file which has data in : YYYYMMDD format. All these columns got inserted as NULL

Thanks in advance
Vipin

This signifies that, date formats for all columns should be same with in Single file!-
Can we load file with two different date type formats in two differnt columns using Matillion?


Example : File 1.txt has below data
Id invname Invdt Filcredt
1 inv1 20170610000000 20170610
2 inv2 20170611000000 20170611


Matillion Agent  

Laura Malins —

Hi Vipin

A Time Format option in the S3 Load component will affect a column loaded to a Datetime (or TimeStamp) in the database. A Date Format will affect a column loaded to a date.

I got your file to load by creating a table as below:

CREATE TABLE “public”.“case4389-2” (“id” INTEGER, “invname” VARCHAR, “invdt” DATETIME, “filcredt” DATE) BACKUP Yes DISTSTYLE EVEN

Then on the S3 Load the Date Format is YYYYMMDD (auto also worked here) and the Time Format is YYYYMMDDHHMISS

Thanks
Laura


Vipinkumar Jha —

Thank you Laura Malins for your kind support.

Post Your Community Answer

To add an answer please login