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
string to date...
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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