Page 1 of 1

string to date...

Posted: Tue Jan 21, 2003 10:06 pm
by rasi
Hi All,

Right now I found a problem which is quite strange. I am using the IConv and Oconv function to convert string into internal date and output to timestamp field.

The function i had written has this code.
Arg1="16-MAY-97

Ans = OConv(IConv(Arg1,"D-DMY[,A3,2]"),"D-YMD[4,2,2]")


The Expected answer is = 1997-05-16

When i run this in datastage 5.2.1(NLS) it gives no result. The result is blank.
Whereas if i run the same thing in Windows 2000 which is different server then it gives the expected answer. Is this related to the Month which is stored inside the database with NLS character. Kindly let me know if anyone had the same problem.


Thanks
Rasi

Posted: Tue Jan 21, 2003 11:36 pm
by ray.wurlod
Two possibilities.

One is that the day specification is not provided. Use "Z" or "2" for Iconv - it doesn't really matter. In fact, the stuff in square brackets is largely ignoreable for Iconv.
Ans = OConv(IConv(Arg1,"D-DMY"),"D-YMD[4,2,2]")

The other possibility is that, for your locale, the month abbreviation is not "MAY" but something else. What locale are you using? For example, if it's French then the month abbreviation would be "MAI".

An observation. Since this can all be done in one line, why not make it a Transform (which becomes in-line code) rather than a Routine (which incurs a small call overhead at run time)?


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Posted: Tue Jan 21, 2003 11:39 pm
by ray.wurlod
For troubleshooting, create a routine that only does the Iconv, and logs the value returned by the Status() function.
You can use the Test grid to observe the result of Iconv. Double click on this Result cell to see anything that might be logged by the function.

FUNCTION TestIconv(Arg1)
Ans = Iconv(Arg1, "D-DMY[,A3,Y]")
Message = "Value of Status() is " : Status()
Call DSLogInfo(Message, "TestIconv")
RETURN(Ans)


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Posted: Wed Jan 22, 2003 11:16 pm
by rasi
Hi Ray,

The status returned value 2. The result is empty. I guess the problem is definitely with the month name. How to solve this.

Thanks
Rasi

Posted: Wed Jan 22, 2003 11:28 pm
by ray.wurlod
Status() of 1 means that the thing to be converted is not valid.
Status() of 2 means that the conversion specification is not valid. That is, in your case, Oconv() has objected to "D-DMY[,A3,Y]". The problem (mine) is that the year specification must not be "Y". So you need to find a formulation that Oconv() will accept, for example "D-DMY[,A3,4]".

If your dates are coming in in English but your locale is something else, run just that Transformer stage with an English locale. You will need to set up before-stage and after-stage subroutines in the stage properties, each executing ExecUV.
In the before-stage, the command is SET.LOCALE TIME US-ENGLISH, and in the after-stage, the command is RESTORE.LOCALE.

You might also find it interesting to see what the alphabetic date components are on your system. Create a Routine that simply does Oconv(Arg1, "D-DMY[2,A,4]") or Oconv(Arg1, "DDMBY") or similar, and test via the Test grid.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Posted: Fri Jan 24, 2003 5:29 am
by rasi
Hi Ray,

I sorted this problem by changing the Default NLS Time/Date to US-English in the Datastage Administrator Project.

Thanks
Rasi