Timestamp Conversion
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 296
- Joined: Sun Nov 16, 2008 7:41 pm
Timestamp Conversion
Hello All
I am reading a field name POLICY_EFF_DT from oracle table using OCI Stage and output is fed to dataset.
POLICY_EFF_DT datatype is Date .
When i try to read the field direclty i got an error like
"No datatype conversion form timestamp to date"
Then i modified the quer like
To_date(policy_eff_dt, 'YYYY:MM:DD HH24:MI:SS').
When i run the job, i got the below error.
The modify operator has a binding for the non-existent output field "POLICY_EFF_DT".
Same query executes successfully in Oracle.
Do i need to any transformations for date field in Parallel edition.
In oracle it looks only Date datatype exists eventhough it has timestamp value.
How can i read this field in OCI stage in parallel.
In OCI Date only takes "YYYY:MM:DD" format.
Do i need to convert date to char and read the field
I am reading a field name POLICY_EFF_DT from oracle table using OCI Stage and output is fed to dataset.
POLICY_EFF_DT datatype is Date .
When i try to read the field direclty i got an error like
"No datatype conversion form timestamp to date"
Then i modified the quer like
To_date(policy_eff_dt, 'YYYY:MM:DD HH24:MI:SS').
When i run the job, i got the below error.
The modify operator has a binding for the non-existent output field "POLICY_EFF_DT".
Same query executes successfully in Oracle.
Do i need to any transformations for date field in Parallel edition.
In oracle it looks only Date datatype exists eventhough it has timestamp value.
How can i read this field in OCI stage in parallel.
In OCI Date only takes "YYYY:MM:DD" format.
Do i need to convert date to char and read the field
You use TO_CHAR() when selecting, not TO_DATE() as it already is one in Oracle as thus only used when loading data into a DATE field. Then the appropriate StringTo?? function can convert it to whatever you need, date or timestamp, inside the job depending if you need the time portion or not.
And lose the colons in the date mask, dashes would be more appropriate there.
And lose the colons in the date mask, dashes would be more appropriate there.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
the vital response...
If you want just the date, do a
to_char(<field>, 'YYYY-MM-DD') policy_blah_blah
i.e. leave out the time part as DataStage will expect a timestamp datatype if time is specified.
An Oracle date still holds the time field and is therefore actually a timestamp.tsamui wrote:Change the POLICY_EFF_DT data type to Timestamp. I think OCI stage will able to read the date properly.
Actually if you import the metadata of the table by 'Import Table Definition' functionality, the data type of the column will be Timestamp.
If you want just the date, do a
to_char(<field>, 'YYYY-MM-DD') policy_blah_blah
i.e. leave out the time part as DataStage will expect a timestamp datatype if time is specified.
-
- Participant
- Posts: 3
- Joined: Tue May 05, 2009 11:57 pm
I have the same problem,in Oracle the EFF_DT field has a DATE datatype.When i try to use in an Oracle Enterprise stage and supplying the below query
SELECT to_char(PIP.EFF_DT,'YYYY-MM-DD') as START_DT
FROM table
it is giving the error
Error when checking operator: When binding output interface field "START_DT" to field "START_DT": No default type conversion from type "ustring[max=75]" to type "date".
Help me!!
SELECT to_char(PIP.EFF_DT,'YYYY-MM-DD') as START_DT
FROM table
it is giving the error
Error when checking operator: When binding output interface field "START_DT" to field "START_DT": No default type conversion from type "ustring[max=75]" to type "date".
Help me!!
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
First try to go through the error message where it says the columns' datatype is Date and by using to_char() you are passing string to it.SELECT to_char(PIP.EFF_DT,'YYYY-MM-DD') as START_DT
FROM table
Use to_date() or change the field type as varchar.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 3
- Joined: Tue May 05, 2009 11:57 pm