Date Conversion

sri75 · Post by **sri75** » Thu May 03, 2007 1:28 pm

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

kripakrish · Post by **kripakrish** » Thu May 03, 2007 1:33 pm

I think you have to use to_date(value,'format') for oracle.
Gurus please confirm.

DSguru2B · Post by **DSguru2B** » Thu May 03, 2007 1:33 pm

Change it to YYYY-MM-DD format using ICONV/OCONV and then send it to oracle as date.

sri75 · Post by **sri75** » Thu May 03, 2007 1:36 pm

Hi,

I used this function ,it is working now

Iconv(linkname.columnname, "DMDY")

chulett · Post by **chulett** » Thu May 03, 2007 1:39 pm

That... can't be working, unless 'working' just means not blowing up. Have you checked in Oracle and see the same dates there as were sent? I'm asking because the OCI stages don't take dates in internal format.

sri75 · Post by **sri75** » Thu May 03, 2007 1:51 pm

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 ?

chulett · Post by **chulett** » Thu May 03, 2007 2:13 pm

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.

sri75 · Post by **sri75** » Thu May 03, 2007 2:20 pm

craig,

I removed the derivation.I got this warning

processing column "HIRED"
Value treated as NULL
Attempt to convert String value "11/03/07" to Date type unsuccessful

it put null value to that column.

Can you please advice me what needs to be done here

Thanks

chulett · Post by **chulett** » Thu May 03, 2007 2:27 pm

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:

Code: Select all

OConv(Iconv(DSLink71.HIRED, "DMDY"),"D-YMD[4,2,2]")

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.

sri75 · Post by **sri75** » Thu May 03, 2007 6:42 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu May 03, 2007 6:48 pm

The suggested second argument for Oconv() was "D-YMD[4,2,2]" but you used "D-DMY[2,2,2]" against that advice. Why?

sri75 · Post by **sri75** » Thu May 03, 2007 7:13 pm

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

chulett · Post by **chulett** » Thu May 03, 2007 7:57 pm

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:

Code: Select all

Oconv(Iconv(DSLink71.HIRED, "DDMY"),"D-DMY[2,A3,2]")

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.

DSguru2B · Post by **DSguru2B** » Fri May 04, 2007 6:48 am

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

?

chulett · Post by **chulett** » Fri May 04, 2007 7:27 am

Nope.