How to retrieve Date from a hash file(shared Container)

pmadhavi · Post by **pmadhavi** » Thu Feb 23, 2006 10:31 am

Hi,
We are using DS7.5 for Peoplesoft upgarde project.
I am passing the MaxDateTime value to a shared container StoreMaxLastUpdDttm , in trun it is getting stored in a hash file. The datatype of the MaxDateTime is Timestamp(19).

Now I am trying t retrieve the date using '#LastModifiedDateTime#' (delievered by Peoplesoft for incremental Jobs)

I am not able to find out in which format it is getting stored in the hash file
because If i try to convert it into date using TO_DATE(), it is throwing an error like
(full) year must be between -4713 and +9999, and not be 0

If I try to convert it into char using to_char(), it is throwing an error like
ORA-01722: invalid number

Pls let me know do we have any other alternate to retriev date from the hash file/shared container

[/b]

ArndW · Post by **ArndW** » Thu Feb 23, 2006 10:45 am

There are no conversions performed when you write to a hashed file. If the datatype is timestamp(19) it most likely is already in string display format and is stored that way. The easiest thing for you to do is to go into the shared container and do a "view data" on the hashed file contents. What is displayed to you there is going to be the same format you need to look it up with.

pmadhavi · Post by **pmadhavi** » Thu Feb 23, 2006 10:59 am

I check the data in the hash file and the date column looks like
'YYYY-MM-DD HH24:MI:SS'

But when we try to compare it with the date column of a table using LastModifiedDateTime, its now working.

So I am trying to convert it to date before comparing.
But that is also not working

ArndW · Post by **ArndW** » Thu Feb 23, 2006 12:22 pm

Madhavi,

the most frustrating thing is hearing "it's not working" without getting a description of what isn't working; that doesn't help solve a problem.

You have now confirmed that the hashed file contains the column in string form in the format of "YYYY-MM-DD HH24:NN:SS".

Now you need to find out what format your "LastModifiedDateTime" field expects the data to be in. Where is this column being used as a lookup? In a custom SQL statement going to oracle?

If it is SQL then use a date/time that you know exists and do a manual SQL SELECT using the format of the hashed file until you get the SELECT to complete correctly. Most likely your TO_DATE() isn't working correctly; but you can easily confirm it by testing it outside of DataStage (remember, keep things simple and distill complex seeming problems into simple little components)