Date format issue

[SwathiCh]

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,

[vinothkumar]

You should be using sv1=Field(pFilename,"_",2,1)
to get the date from file name..

[SwathiCh]

Hi vinoth ,

Iam sorry for printing wrongly in the post,

I did correctly in the job like sv1=Field(pFilename,"_",2,1) .still it is loading as 2/16/4698

[vinothkumar]

Did you use StringToDate to convert String to a date before loading

[SwathiCh]

Iam doing a server job. I don't see any string conversion functions in the tranformer.
Can please suggest me is there any workaround?

[vinothkumar]

Then use to_date() function in Oracle Stage while inserting..

[chulett]

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.

[SwathiCh]

Thank's craig, It solved my issue.