date format from flatfile to oracle database
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 76
- Joined: Wed Jan 25, 2006 6:42 pm
date format from flatfile to oracle database
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.
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.
ETL
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
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.
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.
-
- Participant
- Posts: 76
- Joined: Wed Jan 25, 2006 6:42 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.
Oracle accepts date format in format . The derivation 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?
Code: Select all
YYYY-MM-DD HH24:MI:SS
Code: Select all
Oconv(Iconv(ColumnName, "D-DMY[2,2,4]") , "D-YMD[4,2,2]")
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 76
- Joined: Wed Jan 25, 2006 6:42 pm
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
How about a derivation of
and then let auto-generated SQL add the to_date for you automatically?
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]
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle