Loading char date into OCI9 Date datatype (not timestamp)

[IslandMon]

Does anyone know exactly what to code to load a char date into OCI9 datatype. I'm getting the following error:
Attempt to convert String value "12/01/2002" to Date type unsuccessful

[Sainath.Srinivasan]

Try OConv(YourDate, 'D-YMD[4,2,2]')

[talk2shaanc]

Alternate soln.
Take the value as character(having Varchar(10)) through-out all the stages of the job(even in the OCI stage, have it as varchar) and in the SQL query, use date function with format matching with the input pattern.
ex:
Col1=varchar(10)=12/01/2002

Code: Select all

seq-->Xfm-->OCI(table name: T1)

Write user defined query,
insert into T1(col1,col2......) values (to_date(:1,'MM/DD/YYYY'),:2,......)

[talk2shaanc]

Try OConv(YourDate, 'D-YMD[4,2,2]')

Format does not match with the input value. Looking at the value he has pasted, it should be either OConv(YourDate, 'D-DMY[2,2,4]') or OConv(YourDate, 'D-MDY[2,2,4]')

[Sainath.Srinivasan]

To be clear, first you must format your date to internal format by using IConv(YourStringDate, "D/DMY[2,2,4]") and then use the mention OConv as OConv(DSInternalDateFmt, "D-YMD[4,2,2]").

[IslandMon]

installed code: oconv(iconv(NewDate, "D-DMY"),"D-YMD[4,2,2]")

I'm still getting error: Value treated as NULL
Attempt to convert String value "2002-12-01" to Date type unsuccessful

Target field is Date - length of 10

[IslandMon]

It appears that the only method for loading is into a timestamp. I don't believe it's possible to load only the date. The OCI9 stage always attempts to load the DATE as TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'), which is not a DATE field, but is really a TIMESTAMP.

As previously stated, I'm getting error:
Value treated as NULL
Attempt to convert String value "2002-12-01" to Date type unsuccessful

Has anyone actually loaded into a DATE datatype???

[Sainath.Srinivasan]

Try only the IConv alone.

[talk2shaanc]

Well if you follow the method that i have suggested in my first post, you would surely get it.

[roy]

Hi,

When I import a date column I get a timestamp in my table definition
I used a transform:

Code: Select all

(Oconv(@DATE, "D-YMD[4,2,2]") : " " : Oconv(@TIME, "MTS"))

to load my job's start date.

I'm working with oci 8 libraries though.

IHTH,

[chulett]

It appears that the only method for loading is into a timestamp. I don't believe it's possible to load only the date. The OCI9 stage always attempts to load the DATE as TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'), which is not a DATE field, but is really a TIMESTAMP.

No, a TIMESTAMP is a completely different animal in Oracle. A DATE field in Oracle always includes a time portion (whether you see it or not) and is best handled in DataStage as a Timestamp datatype. IMHO. When you say you want to "only load the date" that means you should be providing a zero time portion to the date field in your job - and this is what the OCI stage is expecting. As you noted, you can tell that from the mask the TO_DATE command uses.

So... if you get your date into YYYY-MM-DD format first and then simply concatenate " 00:00:00" onto it (note the space as the first character) it will load into a DATE field defined as a Timestamp in the OCI stage just fine.

[IslandMon]

It appears that the only method for loading is into a timestamp. I don't believe it's possible to load only the date. The OCI9 stage always attempts to load the DATE as TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'), which is not a DATE field, but is really a TIMESTAMP.

No, a TIMESTAMP is a completely different animal in Oracle. A DATE field in Oracle always includes a time portion (whether you see it or not) and is best handled in DataStage as a Timestamp datatype. IMHO. When you say you want to "only load the date" that means you should be providing a zero time portion to the date field in your job - and this is what the OCI stage is expecting. As you noted, you can tell that from the mask the TO_DATE command uses.

So... if you get your date into YYYY-MM-DD format first and then simply concatenate " 00:00:00" onto it (note the space as the first character) it will load into a DATE field defined as a Timestamp in the OCI stage just fine.

My goal is to reduce the byte count from 38 to 10, but yet staying in DATE format, allowing date manipulation, allowing other reporting tools to understand the date (thus the reason for not using internal format). Loading 00:00:00 in for time is merely a workaround that does not truly resolve the issue. It appears to be a hole in datastage.

[chulett]

My goal is to reduce the byte count from 38 to 10, but yet staying in DATE format, allowing date manipulation, allowing other reporting tools to understand the date (thus the reason for not using internal format). Loading 00:00:00 in for time is merely a workaround that does not truly resolve the issue. It appears to be a hole in datastage.

No, it's neither a workaround nor a hole. Oracle DATE fields have a time portion. Period. In my opinion, you are much better off explicitly controlling what goes in there and the simplest way to do that is to make sure it's all zeroes when you don't want it to look like it has a time. And trust me, "other reporting tools" will still understand the date.

There are other ways to load date fields, ones that require custom sql or a reliance on the NLS date format of the target database but those will just cause other problems. This way works 100% of the time regardless of the NLS date format of the target database.

It's no skin off my nose if you don't want to take this path. I'm just passing on what has worked well for me (and my clients) for years when dealing with Oracle and DataStage - to consistantly handle all date fields as Timestamp datatypes and to always explicitly control what goes in the time portion.

[amsh76]

I came across same error with the UDB stage, and to resolve this problem, I had inserted the date field in internal format and it worked fine for me.

But after reading Craig's post, I think it might me the same case for DB2 where one need to add the time part for the date field. Let me play around and see..

[chulett]

My advice is specific to Oracle and the strange way that dates have an embedded time portion in them. I know from reading the forum that DB2 is the exception to the 'external' format rule and the only stage that seems to require dates be in internal format when you send them to it - so you had that one right.