Date format problem

[vsi]

Hi,
my source is flat file and target is Oracle iam trying to convert my date format: 20060815 to 15-AUG-2006 by using Oconv and iconv functions as:

oconv(iconv(date, "DYMD"), "D-DMY[2,A3,4]")

but iam getting Error as: Attempt to convert String value " 15-AUG-2006 " to Timestamp type unsuccessful
anybody please answer me.

Thanks in advance

[kris007]

What are your datatypes in source and target? In Btw, try this

Code: Select all

Oconv(Iconv(date[1,4]:'-' :date[5,2]:'-':date[7,2],"DYMD"),"D-DMY[2,A3,4]") 

[thumsup9]

FYI you will have to validate your source date before converting it. If your source date can be NULL or Empty or some invalid date then you need to handle that before conversion.

[chulett]

but iam getting Error as: Attempt to convert String value " 15-AUG-2006 " to Timestamp type unsuccessful

That would be because the format you chose is not appropriate for a Timestamp datatype. You would need to convert it to YYYY-MM-DD format, not DD-MON-YYYY. Technically, the OCI stage is expecting this for a Timestamp:

Code: Select all

YYYY-MM-DD HH24:MI:SS

[vsi]

Hi, Kris
source datatype is: varchar, Target datatype: date

Thanks

[DeepakCorning]

That explains it , Chulett is correct...the format that you converted in is not compatible for the target stage..

[DSguru2B]

See if this ICONV works. DOnt have access to DataStage so cannot test it at the moment. Try it

Code: Select all

OCONV(ICONV(Date, "DYMD[4,2,2]": @VM : "MCN"),"D-DMY[2,A3,4]")

[ray.wurlod]

Your date is OK, but it's not a timestamp. Append " 00:00:00" to it using concatenation.

[chulett]

Sorry, but that date is not ok if the field has been declared as a Timestamp in the job and the stage is generating the DML. And I've already posted what the stage expects in that case.

[DSguru2B]

Hi, Kris
source datatype is: varchar, Target datatype: date

Thanks

Ok, now i am confused. The OP says the target type is 'date', Craig you say its timestamp. What is it???

[ray.wurlod]

The error message mentioned "convert to timestamp".

[chulett]

Which typically means that the Oracle target type is a DATE field with the DataStage datatype declared as a Timestamp, the default when importing metadata via the OCI plug-in. With that combination and generated SQL, the stage wraps a TO_DATE around the field:

Code: Select all

TO_DATE(YourField,'YYYY-MM-DD HH24:MI:SS')

Now, if you define your DataStage job's datatype as a Date, the generated DML is different:

Code: Select all

TO_DATE(YourField,'DD-MON-YY')

The OP would need to clarify what their situation is. The moral of the story is the fact that you need to be cognizant of how the OCI stages work for the various datatypes - especially DATE fields - and deliver the data in whatever format the stage is expecting. BTW, this is spelled out in the .pdf documentation for the stage.

[soapbox]
In my opinion, it's best to adopt a standard methodology that all developers use when dealing with Oracle dates, one that leverages the behaviour of the stage. Enforce that via a series of Oracle date handling routines. I don't believe any approach that treats dates as Varchars and relies on the NLS_DATE format of the database is a valid approach and is fraught with peril. One that takes advantage of the stage and standardizes on an ISO Standard Timestamp format - including a zero timestamp when the date "doesn't have" a time portion - is the only way to go. It will consistantly work regardless of the NLS_DATE format of the Oracle database and make your DATE field woes a thing of the past.
[/soapbox]

[vsi]

Hi, craig

i have tried this: in 7.5 it works fine:
oconv(iconv(DSLink3.HIREDATE, "DYMD"), "D-YMD[4,2,2]")
with datatype: Timestamp

but its not working with 4.2 version, can you suggest in this.

Thanks

[chulett]

Well... 4.2 is pretty old so I don't remember all the gory details about how it worked back then. Explain what 'not working' means. What SQL does the stage generate, what errors do you get, etc.

[vsi]

iam getting the error:
Attempt to convert String value " 2006-08-28 " to Timestamp type unsuccessful

Thanks