Page 1 of 1
date format from flatfile to oracle database
Posted: Mon Sep 11, 2006 2:45 pm
by vardhan354
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.
Posted: Mon Sep 11, 2006 2:54 pm
by thumsup9
Try this:
oconv(iconv(Linkname.columnname, "D-MDY[2,2,4]"), "D-YMD[4,2,2]"):"00:00:00"
Posted: Mon Sep 11, 2006 2:59 pm
by DeepakCorning
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.
Posted: Mon Sep 11, 2006 3:10 pm
by thumsup9
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'
Posted: Mon Sep 11, 2006 3:10 pm
by vardhan354
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
Posted: Mon Sep 11, 2006 3:18 pm
by thumsup9
It should be D-DMY
Posted: Mon Sep 11, 2006 3:19 pm
by thumsup9
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.
Posted: Mon Sep 11, 2006 3:28 pm
by kris007
Oracle accepts date format in format
. 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?
Posted: Mon Sep 11, 2006 3:30 pm
by vardhan354
The target (oracledatabase) "DATE"
Posted: Mon Sep 11, 2006 3:34 pm
by DeepakCorning
Should be DMY , I think he mentioned it is a timestamp in the DB...
Posted: Mon Sep 11, 2006 4:13 pm
by ray.wurlod
Whatever you provide it must match the date picture specified for Oracle.
Posted: Thu Oct 05, 2006 10:11 am
by NBALA
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
Posted: Thu Oct 05, 2006 10:30 am
by kcbland
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?
Posted: Thu Oct 05, 2006 11:13 am
by NBALA
Thanks a lot Ken!
Wow ! It worked Great !
Thanks again.
-NB