Problem when dealing with Date

[balajimadhav]

Hi all,

my server job is a oracle -> transformer-> oracle stage. in source i have three columns as date,month(eg: JAN,DEC...format),year(all three of varchar2 datatype) but in target these three should be mapped to a date column in target. so i used a routine to map these source columns to target. but when it is inserted into the target table i am getting an error
"ORA-01858: a non-numeric character was found where a numeric was expected"

my requirement in short:
source
c1 c2 c3

eg: 10 MAR 2000

target

10-MAR-2000

(where c1,c2,c3 are column 1,2,3 respectively)

thanks in advance.

[LNarayana]

Convert the target date in YYYY-MM-DD format which is compatible with Oracle.

Narayana

[mcolen]

Output format is YYYY-MM-DD 00:00:00 for Oracle dates. The way you have the date listed I would do an Iconv on the Input and Oconv to Oracle Format

[meena]

Hi balajimadhav ,
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).

Code: Select all

my requirement in short: 
source 
c1 c2 c3 

eg: 10 MAR 2000 

target 

10-MAR-2000

Try doing a concatenation of the three columns.
output col=c1:"-":c2:"-"c3.

Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.

[meena]

And also try to use Iconv and Oconv as " mcolen" mentioned.

Hi balajimadhav ,
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).

Code: Select all

my requirement in short: 
source 
c1 c2 c3 

eg: 10 MAR 2000 

target 

10-MAR-2000

Try doing a concatenation of the three columns.
output col=c1:"-":c2:"-"c3.

Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.

Thank you

[balajimadhav]

And also try to use Iconv and Oconv as " mcolen" mentioned.

Hi balajimadhav ,
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).

Code: Select all

my requirement in short: 
source 
c1 c2 c3 

eg: 10 MAR 2000 

target 

10-MAR-2000

Try doing a concatenation of the three columns.
output col=c1:"-":c2:"-"c3.

Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.

Thank you

Hi all,
thanks for your Suggestion. i concatenated all the three columns in the format yyyy-mm-dd but still i got the error and i did a Iconv and Oconv. i got the result as required.

thank you.

[kumar_s]

Concatinate as you specified, and have this funciton in oracle load stage.
to_date('10-MAR-2000','DD-MON-YYYY')

[chulett]

Bottom line is it really doesn't matter what order you stick the pieces together in, or what delimiter you use - as long as it matches the format mask used in the OCI stage.

Best Practice is to stick with ISO formatted dates, those in YYYY-MM-DD format, and let the OCI stage automatically generate the appropriate sql for you. None of this pain-in-the-ass-to-maintain custom sql just for that.