String to Timestamp Conversion in .CSV file as a Source
Posted: Mon Sep 17, 2007 10:56 pm
hi
I am using .CSV file as a source and Oracle as Target.
Source format is dd/mm/yyyy(String) and target format is Timestamp it doesn't matter the format in target but it should be Timestamp value...
I am using Oracle Enterprice stage for Target.
I didn't specified the column size in the Source,beacouse it is a .CSV file.
This is the complete vale i specified in the transformation.
Column Name SQL-Type Length Scale Nullable Description
VALUDATION_DT Timestamp - - Yes Nullable TimeStamp
but when i am doing the same with fixed length file the following code is working,but in case os .CSV file it showing warning that Data may be lost so this methord are not updating the data in target table.
I tried in two way:---
First way is-------
I create one staging variable mydate(String) and use the rite the Query:--
SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2] : " 00:00:00"
and after that in VALUDATION_DT feild i wrote the following Querry:--
StringToTimestamp(mydate,"%yyyy-%mm-%dd %hh:%nn:%ss")
Second way is-----
I wrote the querry directly to VALUDATION_DT feild:--
StringToTimestamp(SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2],"%yyyy-%mm-%dd %hh:%nn:%ss")
But data is not loadinfg to thr target Table...
Thks & Regards,
Sandeep
I am using .CSV file as a source and Oracle as Target.
Source format is dd/mm/yyyy(String) and target format is Timestamp it doesn't matter the format in target but it should be Timestamp value...
I am using Oracle Enterprice stage for Target.
I didn't specified the column size in the Source,beacouse it is a .CSV file.
This is the complete vale i specified in the transformation.
Column Name SQL-Type Length Scale Nullable Description
VALUDATION_DT Timestamp - - Yes Nullable TimeStamp
but when i am doing the same with fixed length file the following code is working,but in case os .CSV file it showing warning that Data may be lost so this methord are not updating the data in target table.
I tried in two way:---
First way is-------
I create one staging variable mydate(String) and use the rite the Query:--
SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2] : " 00:00:00"
and after that in VALUDATION_DT feild i wrote the following Querry:--
StringToTimestamp(mydate,"%yyyy-%mm-%dd %hh:%nn:%ss")
Second way is-----
I wrote the querry directly to VALUDATION_DT feild:--
StringToTimestamp(SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2],"%yyyy-%mm-%dd %hh:%nn:%ss")
But data is not loadinfg to thr target Table...
Thks & Regards,
Sandeep