Date Format

jagadam · Post by **jagadam** » Fri Jun 29, 2012 12:04 pm

I have tab delimited source file from that i am getting the date column in the format "dd-mon-yy" or "d-mon-yy" but, i need to load into oracle table with the default format. Plz help me how to achieve this.

jwiles · Post by **jwiles** » Fri Jun 29, 2012 2:26 pm

First, what is the datatype of the table column you will be loading the date into?

Date formats and loading dates into Databases has been discussed and resolved many times in the forum. Please take a little time to use the search function!

Regards,

prakashdasika · Post by **prakashdasika** » Mon Jul 02, 2012 7:33 pm

Read the date field from source file as a varchar field. Use a stringtodate function in a transformer stage to change it to the required format before loading it to database.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 02, 2012 11:14 pm

There's no such thing as a default format for Oracle. You set your own date picture (or the DBA may have done it for you). Let's assume it's YYYY-MM-DD, which is also the ISO 8601 standard.

You now have two problems - how to correctly add the century to your two digit years, and how to reorder the components converting an alphabetic month to a numeric representation.

Use two StringToDate() conversion, one for each of your formats, governed by If..Then..Else to decide which format to convert, and providing an appropriate format string for each.

Once the internal data type is Date, the Oracle Connector should handle it gracefully.