Hi ,
I have a requirement to load date on file name to a date column in oracle table.
Source---> .txt file ,Target - Oracle
My filename is like XYZ_20100630_1.txt.
I parameterized filename and used field function in stage variables to grep date on filename.
sv1=Field(pFilename,"_",3,1)
sv2=svMonth:"/":svDate:"/":svYear
I mapped sv2 to taget date column(date ,Nullable) in the database.
After loading i could see the date loaded as 2/16/4698 in the database.
Can someone help in here.
thanks,
Date format issue
Moderators: chulett, rschirm, roy
Date format issue
--
Swathi Ch
Swathi Ch
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
StringToDate() (and DatetoString) would be PX functions and not applicable in a Server job. The equivalent would be IConv and OConv. However, somewhat as noted, you can put the date into a string format that Oracle is expecting and your target stage should generate the appropriate SQL to do that.
Me, I always stick with a Timestamp datatype and make sure my dates match YYYY-MM-DD HH24:MI:SS before I send them to the database, even if that means I have to append a zero time to my date in the process. That way the TO_DATE() that the stage will generate will always work regardless of the NLS_DATE format of the target.
Me, I always stick with a Timestamp datatype and make sure my dates match YYYY-MM-DD HH24:MI:SS before I send them to the database, even if that means I have to append a zero time to my date in the process. That way the TO_DATE() that the stage will generate will always work regardless of the NLS_DATE format of the target.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers