Date field column loading into Oracle Database?

pkll · Post by **pkll** » Sat Jan 18, 2014 9:31 am

Hi All,

I have input is

Date,Time_Posted
------------------
8th November2013,12.30 midnight
21th July2013,8.30pm
13th December2013,8.00pm
9th May2013,12.30 midnight
11th April2013,12.30 midnight.

I want output as
-----------------------
Date,Time_Posted
8-11-2013,12.30 midnight
21-07-2013,8.30pm
13-12-2013,8.00pm
09-05-2013,12.30 midnight
11-04-2013,12.30 midnight

I want to load data into oracle database.

I would appreciate, if somebody can advise.

chulett · Post by **chulett** » Sat Jan 18, 2014 10:15 am

Clarify your source data types... strings? And the target - two fields or one? What are their data type(s)? And is there truly no space between the month and year in your input?

ray.wurlod · Post by **ray.wurlod** » Sat Jan 18, 2014 3:43 pm

Use a Field() function to extract the date (everything before the first comma) and time (everything after that comma). Use Left(), Len() and Right() functions to extract the date components, then use date conversion function(s) to convert to date, and finally a data type conversion function to convert that date to a string. (Or just send the date as a DATE).

pkll · Post by **pkll** » Sat Jan 18, 2014 11:25 pm

Hi Chulett,

My source data types are varchar and target is two fields. my first datatype will be timestamp and second will be varchar and no space between
month and year...

chulett · Post by **chulett** » Sun Jan 19, 2014 10:16 am

Oracle target is a DATE or TIMESTAMP but you don't want any time portion in it? And I'm kind of surprised they have 'midnight' at the end of a 12AM time; what do they do for 12PM, add 'noon'?

Regardless, it looks like the only thing that requires any transformation is your date field and Ray has given plenty of advice on that. And there's some discussion here about loading 'timeless' dates in PX / Oracle.

So give it a shot and let us know what issues and / or specific questions you have.