Date Conversion
Moderators: chulett, rschirm, roy
Date Conversion
HI,
my sourcce is from .csv .in that file date format is like this '11/03/07'
I am trying to load this date filed into oracle table with data type 'date'.I didn't do any conversions.
I got this error
while processing column "HIRED"
Value treated as NULL
Attempt to convert String value "11/03/07" to Date type unsuccessful
here what I am thinking is ,it is already in date format .please advice me what needs to be done
Thanks
Sri
my sourcce is from .csv .in that file date format is like this '11/03/07'
I am trying to load this date filed into oracle table with data type 'date'.I didn't do any conversions.
I got this error
while processing column "HIRED"
Value treated as NULL
Attempt to convert String value "11/03/07" to Date type unsuccessful
here what I am thinking is ,it is already in date format .please advice me what needs to be done
Thanks
Sri
-
- Premium Member
- Posts: 15
- Joined: Mon Sep 18, 2006 10:15 am
I checked in oracle table ,data format is like this
03-NOV-07
03-AUG-07
01-AUG-76
01-JUL-78
01-APR-94
01-FEB-97
03-MAY-07.
my job design is like this
seqfile --------->transformer ---------->OCISTAGE
I defined data type for column 'Hired' as varchar while reading from seq file and in the transformer used
Iconv(DSLink71.HIRED, "DMDY")and defined data type as date.
I noticed in generated sql statment in oci stage there is to_date function.
Am I doing correct ?
03-NOV-07
03-AUG-07
01-AUG-76
01-JUL-78
01-APR-94
01-FEB-97
03-MAY-07.
my job design is like this
seqfile --------->transformer ---------->OCISTAGE
I defined data type for column 'Hired' as varchar while reading from seq file and in the transformer used
Iconv(DSLink71.HIRED, "DMDY")and defined data type as date.
I noticed in generated sql statment in oci stage there is to_date function.
Am I doing correct ?
There is no 'Oracle date format', it's up to the tool you use to decide what external format to show the dates in. By default you get the $NLS_DATE format.
I would guess your 'conversion' is actually doing nothing and passing things through unchanged, which is what happens if the conversion fails. And this just happened to work because you matched the NLS_DATE format the database is expecting. You could verify this be removing the derivation only, pass the 'date' through untouched and see if it still works.
Like I said, it can work but it's hardly what I'd consider a Best Practice.
I would guess your 'conversion' is actually doing nothing and passing things through unchanged, which is what happens if the conversion fails. And this just happened to work because you matched the NLS_DATE format the database is expecting. You could verify this be removing the derivation only, pass the 'date' through untouched and see if it still works.
Like I said, it can work but it's hardly what I'd consider a Best Practice.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Interesting. If you've got something that works, stick with it.
My standard advice would be to use Timestamp datatypes in your jobs, let the stage generate the SQL and build a library of routines all developers use to make date handling across your jobs consistent and error free.
For example, this IConv/OConv pair would convert your date to internal format and then back out in an external format more appropriate for the OCI stage with generated SQL:
This is more what DSGuru meant. Not saying it would work for you - you'd need to make more changes to your job for this. Mostly provided as example.
My standard advice would be to use Timestamp datatypes in your jobs, let the stage generate the SQL and build a library of routines all developers use to make date handling across your jobs consistent and error free.
For example, this IConv/OConv pair would convert your date to internal format and then back out in an external format more appropriate for the OCI stage with generated SQL:
Code: Select all
OConv(Iconv(DSLink71.HIRED, "DMDY"),"D-YMD[4,2,2]")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I applied
Oconv(Iconv(DSLink71.HIRED, "DDMY[2,2,2]"),"D-DMY[2,2,2]") this in transformer
got the below error saying invalid month for HIRED column
HealthCenter_EBR802..Transformer_73:
The value of the row is: NAME = ABBOTT,JOANNE TITLE = DENTAL ASST EMPNO = 269403 STATUS = CLASSIF*04 SX = F YB = 49 DEGREEYR = NOT YPR = NULL LEAVEDATE = NULL HIRED = 08-01-76 FTE_SAL = 50580 FUND1 = 0 FUNDP = 100
I am not able to fine the problem in this format, can you please help me
input value from source is like this 11/03/07
I defined datatype for Hired column is char.because in generated sql it converted to date using to_date function
Thanks
Oconv(Iconv(DSLink71.HIRED, "DDMY[2,2,2]"),"D-DMY[2,2,2]") this in transformer
got the below error saying invalid month for HIRED column
HealthCenter_EBR802..Transformer_73:
The value of the row is: NAME = ABBOTT,JOANNE TITLE = DENTAL ASST EMPNO = 269403 STATUS = CLASSIF*04 SX = F YB = 49 DEGREEYR = NOT YPR = NULL LEAVEDATE = NULL HIRED = 08-01-76 FTE_SAL = 50580 FUND1 = 0 FUNDP = 100
I am not able to fine the problem in this format, can you please help me
input value from source is like this 11/03/07
I defined datatype for Hired column is char.because in generated sql it converted to date using to_date function
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
in generated sql
UPDATE "HCTEST" SET "HIRED"=TO_DATE(:2, 'DD-MON-YY') WHERE "NAME"=:1;
INSERT INTO "HCTEST" ("NAME","HIRED") VALUES (:1,TO_DATE(:2, 'DD-MON-YY'))
to match this format
i put this one
Oconv(Iconv(DSLink71.HIRED, "DDMY[2,2,2]"),"D-DMY[2,2,2]")
IF i put this
Oconv(Iconv(DSLink71.HIRED, "DDMY[2,2,2]"),"D-YMD[4,2,2]")
I GOT this error
HealthCenter_EBR802..Transformer_73: At row 1, link "DSLink74", while processing column "HIRED"
Value treated as NULL
Attempt to convert String value "11-03-07" to Date type unsuccessful
UPDATE "HCTEST" SET "HIRED"=TO_DATE(:2, 'DD-MON-YY') WHERE "NAME"=:1;
INSERT INTO "HCTEST" ("NAME","HIRED") VALUES (:1,TO_DATE(:2, 'DD-MON-YY'))
to match this format
i put this one
Oconv(Iconv(DSLink71.HIRED, "DDMY[2,2,2]"),"D-DMY[2,2,2]")
IF i put this
Oconv(Iconv(DSLink71.HIRED, "DDMY[2,2,2]"),"D-YMD[4,2,2]")
I GOT this error
HealthCenter_EBR802..Transformer_73: At row 1, link "DSLink74", while processing column "HIRED"
Value treated as NULL
Attempt to convert String value "11-03-07" to Date type unsuccessful
My suggestion was for use with a Timestamp datatype, not Date. If your generated SQL is using a TO_DATE mask of 'DD-MON-YY' then you must match that with your derivation:
I can't tell if "11/03/07" is November 3rd or March 11th. Adjust the "DDMY" to be "DMDY" for the former, leave as is for the latter. I'm guessing the latter because of the year and the field name.
Code: Select all
Oconv(Iconv(DSLink71.HIRED, "DDMY"),"D-DMY[2,A3,2]")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The previous phenomenon that the OP was experiencing was weird. As in, sending the date in internal format. You guys think its possible that since the column type is set as "Date", the DSEngine takes the internal conversion and changes to the right format ?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.