Loading char date into OCI9 Date datatype (not timestamp)
Moderators: chulett, rschirm, roy
Loading char date into OCI9 Date datatype (not timestamp)
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
Attempt to convert String value "12/01/2002" to Date type unsuccessful
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
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
Write user defined query,
insert into T1(col1,col2......) values (to_date(:1,'MM/DD/YYYY'),:2,......)
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)
insert into T1(col1,col2......) values (to_date(:1,'MM/DD/YYYY'),:2,......)
Shantanu Choudhary
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
More help required
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
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
further info
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???
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???
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
Hi,
When I import a date column I get a timestamp in my table definition
I used a transform:
to load my job's start date.
I'm working with oci 8 libraries though.
IHTH,
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"))
I'm working with oci 8 libraries though.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Re: further info
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.IslandMon wrote: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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: further info
chulett wrote: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.IslandMon wrote: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.
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.
Re: further info
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.IslandMon wrote: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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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..
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..
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers