issues with To_CHAR function
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 72
- Joined: Wed Dec 12, 2007 2:29 am
issues with To_CHAR function
Hi Friends
We have migrated to a new server.
After migration,We are facing a strange issue
We are using below code to extract day of week from a date.
ToChar('2014-10-01',"D")
It is returning value as 3 in old server where same code is returning 4 on new server.
The values of DataStage environment variables are same in old and new environment
Any ideas,what could be causing this
Any option changed during installation can cause this ?
Appreciate your help
We have migrated to a new server.
After migration,We are facing a strange issue
We are using below code to extract day of week from a date.
ToChar('2014-10-01',"D")
It is returning value as 3 in old server where same code is returning 4 on new server.
The values of DataStage environment variables are same in old and new environment
Any ideas,what could be causing this
Any option changed during installation can cause this ?
Appreciate your help
Not aware of any "ToChar" function so it must be something unique to your site... what is it actually using inside it? Off the top of my head there are settings (a setting?) that controls what day of the week is considered day 1, Sunday or Monday... you may be running afoul of that. LOCALE perhaps? Hopefully someone will know for certain.
In the meantime might be prudent to post your routine code.
In the meantime might be prudent to post your routine code.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 72
- Joined: Wed Dec 12, 2007 2:29 am
Yes. you all are right. Its not a DataStage Function. Its a server routine
Below is the code
Below is the code
Code: Select all
Dimension f(23), v(23)
datepart=Field(date," ", 1)
timepart=Field(date," ", 2)
* dy=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWB")
* day=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWA")
* month=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMA")
* mon=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMB")
dd=date[9,2]
mm=date[6,2]
year=date[1,4]
hour=date[12,2]
minute=date[15,2]
second=date[18,2]
f(1)="DDD"
f(2)="DD"
f(3)="DY"
f(4)="DAY"
f(5)="D"
f(6)="MONTH"
f(7)="MON"
f(8)="MM"
f(9)="YYYY"
f(10)="YYY"
f(11)="YY"
f(12)="Y"
f(13)="WW"
f(14)="W"
f(15)="HH24"
f(16)="HH12"
f(17)="HH"
f(18)="MI"
f(19)="Q"
f(20)="RR"
f(21)="SSSSS"
f(22)="SS"
f(23)="J"
v(1)=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")
If Len(v(1)) = 2 Then
v(1)="0":v(1)
End
If Len(v(1)) = 1 Then
v(1)="00":v(1)
End
v(2)=date[9,2]
v(3)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWB"), "MCT")
v(4)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWA"), "MCT")
v(5)=Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DW"))
v(6)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMA"), "MCT")
v(7)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMB"), "MCT")
v(8)=date[6,2]
v(9)=date[1,4]
v(10)=date[2,3]
v(11)=date[3,2]
v(12)=date[4,1]
If mod(Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")), 7) <> 0 Then
v(13)=Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")/7)+1
End Else
v(13)=Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")/7)
End
If mod(Int(date[9,2]), 7) <> 0 Then
v(14)=Int(date[9,2]/7)+1
End Else
v(14)=Int(date[9,2]/7)
End
hour=date[12,2]
minute=date[15,2]
second=date[18,2]
v(15)=date[12,2]
v(16)=date[12,2]
If Int(date[12,2]) > 12 Then
v(16)=mod(Int(date[12,2]), 12)
End
If Int(date[12,2]) = 0 Then
v(16)="12"
End
v(17)=date[12,2]
If Int(date[12,2]) > 12 Then
v(17)=mod(Int(date[12,2]), 12)
End
If Int(date[12,2]) = 0 Then
v(17)="12"
End
v(18)=date[15,2]
v(19)=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DQ")
v(20)=date[3,2]
v(21)=(Int(date[12,2])*60*60)+(Int(date[15,2])*60)+Int(date[18,2])
v(22)=date[18,2]
v(23)="J"
i=1
OutputDate=format
For i = 1 To 23 Step 1
OutputDate=Change(OutputDate, f(i), v(i))
Next i
Ans=OutputDate
Yikes. Looks like massive overkill just to get the day of the week but I guess it was meant to be generic and one size fits all. Still.
Where's the argument handling? I don't see any mention of Arg1 or Arg2, guessing "date" is Arg1 but no clue where the "D" you were passing in goes... "format"? Is the posted code the complete code?
Where's the argument handling? I don't see any mention of Arg1 or Arg2, guessing "date" is Arg1 but no clue where the "D" you were passing in goes... "format"? Is the posted code the complete code?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Still think it has something to do with your locale setting or something of that nature, whatever OConv uses to determine what day your week starts on. 2014-10-01 was a Wednesday, btw, hence the 3 or 4 depending on your week starting on Monday or Sunday.
Clarify something, please - what is the difference between the "old server" and the "new server"? Anything significant like the O/S flavor or version? Hardware vendor? DataStage version?
Clarify something, please - what is the difference between the "old server" and the "new server"? Anything significant like the O/S flavor or version? Hardware vendor? DataStage version?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 4
- Joined: Fri Sep 02, 2011 9:56 am
Posting on behalf of udayk_2007
Hi,
I am team member of user who posted this topic.
Just to give an update:
The function OConv(IConv(date, "D-YMD[4,2,2]"),"DW") is also returning value as 4 in new environment while in old it is returning 3 when we are passing the date as '2014-10-01'.
Even for the other dates the value is always 1 higher in the new environment.
The Datastage Version,Hardware is same in both the environments,just that the new one is installed on a different server.
Do we have any setting in DataStage which decides whether Sunday should be considered 0 or 1.
Appreciate your help.
I am team member of user who posted this topic.
Just to give an update:
The function OConv(IConv(date, "D-YMD[4,2,2]"),"DW") is also returning value as 4 in new environment while in old it is returning 3 when we are passing the date as '2014-10-01'.
Even for the other dates the value is always 1 higher in the new environment.
The Datastage Version,Hardware is same in both the environments,just that the new one is installed on a different server.
Do we have any setting in DataStage which decides whether Sunday should be considered 0 or 1.
Appreciate your help.
Thanks,
Isha Sharma
Isha Sharma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is, and how that is done depends on whether or not you have NLS enabled.
If you have NLS enabled, then the ordinal day number settings are specified in your locale. Without NLS there is, if I recall correctly, an undocumented setting in the uvconfig file.
Or you could simply subtract 1 from the value, or use a Convert() function to achieve the required wraparound.
You can also use the weekday_from_date() function in the Modify stage, which lets you specify which is the start day (the day zero) of the week.
If you have NLS enabled, then the ordinal day number settings are specified in your locale. Without NLS there is, if I recall correctly, an undocumented setting in the uvconfig file.
Or you could simply subtract 1 from the value, or use a Convert() function to achieve the required wraparound.
You can also use the weekday_from_date() function in the Modify stage, which lets you specify which is the start day (the day zero) of the week.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Posting on behalf of udayk_2007
Sounds like one might have NLS enabled and the other doesn't.sharmaisha0902 wrote:The Datastage Version,Hardware is same in both the environments,just that the new one is installed on a different server.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 72
- Joined: Wed Dec 12, 2007 2:29 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You cannot change the fact that NLS is installed.
However, the NLSMODE parameter in the uvconfig file determines whether NLS is enabled or not.
The usual caveats apply, such as the need to reconfigure shared memory during a restart of the engine having changed the uvconfig file.
However, the NLSMODE parameter in the uvconfig file determines whether NLS is enabled or not.
The usual caveats apply, such as the need to reconfigure shared memory during a restart of the engine having changed the uvconfig file.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 72
- Joined: Wed Dec 12, 2007 2:29 am