DateGenericIsDate is NOT working
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 31
- Joined: Wed Feb 04, 2004 2:04 pm
- Location: Canada
DateGenericIsDate is NOT working
One of our old DataStage job started abending from January 1, 2013 with the following error :
WSWDS044CommonInvoice..S50TransValidate (DateGenericToTimeStamp): Could not convert date/time :20130102
This job uses the 'DateGenericIsDate' function and has the following code in the stage variables :
If RecordIdValid = 'Y' and FromS10WSWT017.SPO_INVC_DATE_FLG ='Y' And FromS05ds04401_at.SPO_INVC_DATE <> space(10)
And DateGenericIsDate(FromS05ds04401_at.SPO_INVC_DATE) = 1 and Substrings(FromS05ds04401_at.SPO_INVC_DATE,1,4) > 1980
and Substrings(FromS05ds04401_at.SPO_INVC_DATE,1,4) < 2025 Then 'Y' Else 'N'
The job worked without any issues till December 31st, 2012. The date format which is being validated is CCYYMMDD. It worked with date 20121231 but did not work with date 20130102. Can someone help in identifying the issue. I noticed that the code uses the function 'DateGenericIsDate' but in the error message, it is displaying 'DateGenericToTimeStamp'. Any help is fixing the issue is appriciated?
Thanks
WSWDS044CommonInvoice..S50TransValidate (DateGenericToTimeStamp): Could not convert date/time :20130102
This job uses the 'DateGenericIsDate' function and has the following code in the stage variables :
If RecordIdValid = 'Y' and FromS10WSWT017.SPO_INVC_DATE_FLG ='Y' And FromS05ds04401_at.SPO_INVC_DATE <> space(10)
And DateGenericIsDate(FromS05ds04401_at.SPO_INVC_DATE) = 1 and Substrings(FromS05ds04401_at.SPO_INVC_DATE,1,4) > 1980
and Substrings(FromS05ds04401_at.SPO_INVC_DATE,1,4) < 2025 Then 'Y' Else 'N'
The job worked without any issues till December 31st, 2012. The date format which is being validated is CCYYMMDD. It worked with date 20121231 but did not work with date 20130102. Can someone help in identifying the issue. I noticed that the code uses the function 'DateGenericIsDate' but in the error message, it is displaying 'DateGenericToTimeStamp'. Any help is fixing the issue is appriciated?
Thanks
Harsh Kohli
-
- Participant
- Posts: 31
- Joined: Wed Feb 04, 2004 2:04 pm
- Location: Canada
DateGenericIsDate is under Transform -> sdk -> date -> Generic in manager/Designer. When I double click DateGenericIsDate, it does not give any code but under 'Details' tab under 'definition' it has the following :
(If DateGenericToTimeStamp(%InputDate%, 0) # "" Then 1 Else 0)
It seems it is using 'DateGenericToTimeStamp' function.
(If DateGenericToTimeStamp(%InputDate%, 0) # "" Then 1 Else 0)
It seems it is using 'DateGenericToTimeStamp' function.
Harsh Kohli
-
- Participant
- Posts: 31
- Joined: Wed Feb 04, 2004 2:04 pm
- Location: Canada
'DateGenericToTimeStamp' is located under Routines -> sdk -> date and has the following code :
Code: Select all
*************************************************************************
* Copyright (C) 2004, 1997-2003 Ascential Software Corporation. All Rights Reserved. *
* This code may be copied on condition that this copyright *
* notice is included as is in any code derived from this source. *
*************************************************************************
*
* Routine takes many date formats and converts them to a common output format;
* the SDK timestamp format "YYYYMMDD HH:MM:SS.SSS".
*
* Expected input formats:
* If Arg2 = 1 then format of Date is expected to be (CC)YY MM DD with delimiters.
* If Arg2 = 0 then format of the date is expected as stated below.
* - Any delimited date with Month Day Year, e.g. 4/19/1999, 4.19.1999, 4/19/99, 4.19.99
* - Alpha month dates ie. Apr 19 1999, Apr 19 99
* - Non-delimited date with Year Month day e.g. 19990419, 990419
* - Jullian Year Days ie. 99126, 1999126
* These could also have a time entry, e.g. 10:30:49.000, 10:30:49 or 10:30
RoutineName = 'DateGenericToTimeStamp'
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
*
* Following variables are for Y2K automation:
* Y2KDate is the year to 99 that will use the Y2KCentury
* If year coming in is less then Y2KDate then use Y2KCentury + 1
Y2KDate = 30
Y2KCentury = 19
* Remove extra spaces, and count number of spaces remaining
PDate = Trim(Arg1)
NumbofSpace = Count(PDate," ")
Begin Case
Case NumbofSpace = 1
IDate = Field(PDate," ",1)
ITime = Field(PDate," ",2)
Case NumbofSpace = 3
Pos = Index(PDate," ",3)
IDate = PDate[1,Pos-1]
ITime = Field(PDate," ",4)
Case 1
If Index(PDate,":",1) Then
IDate = Oconv(@date,"D")
ITime = PDate
End Else
IDate = PDate
ITime = "00:00:00.000"
End
End Case
Begin Case
Case Len(ITime) = 5
ITime = ITime:":00.000"
Case Len(ITime)=8
ITime = ITime:".000"
Case Len(ITime)=11
ITime = ITime:"0"
End Case
If not(isnull(Arg1)) then
If Arg2 then
IDate = Iconv(IDate,"DYMD[2,2,2]")
End Else
IDate = Iconv(IDate,"D")
End
If Status() or Field(ITime,":",1) > 23 or Field(ITime,":",2) > 59 or Field(ITime,":",3) > 59.999 Then
Message = DSRMessage("DSTAGE_TRX_E_0015", "Could not convert date/time :%1", Arg1)
CALL DSLogWarn(Message, RoutineName)
Ans = ""
End Else
Year = Oconv(IDate,"DY")
If Index(Arg1,Year,1) else
Year = Oconv(IDate,"D2Y")
Month = Oconv(IDate,"DM")
Day = Oconv(IDate,"DD")
If Year GE Y2KDate then
Year = Y2KCentury:Year
End Else
Year = Y2KCentury+1:Year
End
IDate = Iconv(Year:"-":Month:"-":Day,"D-YMD[4,2,2]")
End
Ans = Trim(Oconv(IDate,"DYMD[4'',2'',2'']")," ","A"):" ":ITime
End
End Else
Ans = Arg1
End
Harsh Kohli
Any luck with this? I have absolutely no ability to test stuff like this any more and a quick eyeball of the code doesn't reveal anything to me that looks like it would care about the fact that you've rolled over into 2013.
Any chance that there's more to the data than just "20130102", like extra spaces or unprintable characters?
Any chance that there's more to the data than just "20130102", like extra spaces or unprintable characters?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
It would seem that will correctly convert, while won't.
Code: Select all
ICONV('20121231','D')
Code: Select all
ICONV('20130102','D')
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
So, it would seem by observation or by testing? I wonder if we've passed some "far future" threshold for the original coding of that function... if that's the case (that the simple test Arnd posted fails) then it seems like something to get in front of your official support provider.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I tested it, and it fails. I don't have any docs to check whether this is expected behaviour or not, but I don't believe it is.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 31
- Joined: Wed Feb 04, 2004 2:04 pm
- Location: Canada
While testing, I set the time/date locale for this job to OFF and then recomplied the program and now the job does not give any errors. Earlier the time/date locale under NLS was set to 'US-English' :
Edit -> Job Properties -> NLS Tab -> Default Locale Categories -> turn Time/Date to OFF
I still have to validate the output file but atleast the job is not giving errors with Jan 2013 dates.
The description of the function 'DateGenericIsDate' also says the following :
In an NLS environment, this transform will not work for most formats, unless the time/date locale for the job is set to OFF.
Edit -> Job Properties -> NLS Tab -> Default Locale Categories -> turn Time/Date to OFF
I still have to validate the output file but atleast the job is not giving errors with Jan 2013 dates.
The description of the function 'DateGenericIsDate' also says the following :
In an NLS environment, this transform will not work for most formats, unless the time/date locale for the job is set to OFF.
Harsh Kohli
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: