date format from flatfile to oracle database

vardhan354 · Post by **vardhan354** » Mon Sep 11, 2006 2:45 pm

Hi,

first scenario:-

source database:- oracle date(column name) --- varchar

target database:- flatfile date(column name) ---- varchar

can i use any function for this.

Second:-

source flatfile:- date (column name):- varchar
Target database:- oracle date(column name):- Time stamp.

i am using this function as

oconv(iconv(Linkname.columnname, "D-DMY[2,2,4]"), "D-YMD[4,2,2]"):"00:00:00

the source format in flatfile is :- DD-MM-YYYY.

Third:-

source flatfile:- date column (varchar):-
Target :- oracle;- date column (Timestamp)

i am using function as

oconv(iconv(Linkname.columnname, "D-DMY[2,2,4]"), "D-YMD[4,2,2]"):"00:00:00"

The flatfile (date format) is DD-MM-YYYY.

Please help me that i am using correct functions in my,
when i am using the above function the date columns records are not loading into the target.

Thanks in advance.

thumsup9 · Post by **thumsup9** » Mon Sep 11, 2006 2:54 pm

Try this:

oconv(iconv(Linkname.columnname, "D-MDY[2,2,4]"), "D-YMD[4,2,2]"):"00:00:00"

DeepakCorning · Post by **DeepakCorning** » Mon Sep 11, 2006 2:59 pm

If you can take care of the spaces in the function then it will work fine. I see some spaces missing (Can not say for sure as I was not able to read it very properly)
Correct one --
Oconv(Iconv(ColumnName, "D-DMY[2,2,4]") , "D-YMD[4,2,2]")

Also I think so even if you do not append the Time then also Oracle Target will take care of it.

thumsup9 · Post by **thumsup9** » Mon Sep 11, 2006 3:10 pm

thumsup9 wrote:Try this:

oconv(iconv(Linkname.columnname, "D-MDY[2,2,4]"), "D-YMD[4,2,2]"):"00:00:00"

Sorry, you said the format was DD-MM-YYYY, check this

oconv(iconv(Linkname.columnname, 'D-MDY[2,2,4]'), 'D-YMD[4,2,2]') : ' 00:00:00'

vardhan354 · Post by **vardhan354** » Mon Sep 11, 2006 3:10 pm

Thanks for the response.

Hi deepak/Thumsup9,

please suggest me which is the exact format

D-DMY or D-MDY

and i also checked there are no spaces in the function.

i tried for both,but it is not loading any data.

please help me .

Thanks in advance

thumsup9 · Post by **thumsup9** » Mon Sep 11, 2006 3:18 pm

It should be D-DMY

thumsup9 · Post by **thumsup9** » Mon Sep 11, 2006 3:19 pm

vardhan354 wrote:Thanks for the response.

Hi deepak/Thumsup9,

please suggest me which is the exact format

D-DMY or D-MDY

and i also checked there are no spaces in the function.

i tried for both,but it is not loading any data.

please help me .

Thanks in advance

Vardhan,

Whats the format on the database. Can you please check the format.

kris007 · Post by **kris007** » Mon Sep 11, 2006 3:28 pm

Oracle accepts date format in format

Code: Select all

YYYY-MM-DD HH24:MI:SS

. The derivation

Code: Select all

Oconv(Iconv(ColumnName, "D-DMY[2,2,4]") , "D-YMD[4,2,2]")

should work fine as long as you have given the input date format correct in your earlier post. Can you confirm your input date format once again?

vardhan354 · Post by **vardhan354** » Mon Sep 11, 2006 3:30 pm

The target (oracledatabase) "DATE"

DeepakCorning · Post by **DeepakCorning** » Mon Sep 11, 2006 3:34 pm

Should be DMY , I think he mentioned it is a timestamp in the DB...

ray.wurlod · Post by **ray.wurlod** » Mon Sep 11, 2006 4:13 pm

Whatever you provide it must match the date picture specified for Oracle.

NBALA · Post by **NBALA** » Thu Oct 05, 2006 10:11 am

Hi,

Any one please help me !

I have a date and time format like '20061003191438' (yyyymmddHHmmss) as input date from source. I want to load this in Oracle date.

I have tried with 'DateYearFirstToOraOCIWithTime' but its returnig with some date with charaters like 2016-BC-07...

Any suggestion appreciated.

Thanks
-NB

kcbland · Post by **kcbland** » Thu Oct 05, 2006 10:30 am

How about a derivation of

Code: Select all

inlink.col[1,4]:"-":inlink.col[5,2]:"-":inlink.col[7,2]:" ":inlink.col[9,2]:":":inlink.col[11,2]:":":inlink.col[13,2]

and then let auto-generated SQL add the to_date for you automatically?

NBALA · Post by **NBALA** » Thu Oct 05, 2006 11:13 am

Thanks a lot Ken!

Wow ! It worked Great !

Thanks again.
-NB