DateGenericIsDate is NOT working

harshkohli · Post by **harshkohli** » Fri Jan 04, 2013 9:25 am

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

chulett · Post by **chulett** » Fri Jan 04, 2013 10:23 am

That sounds like a custom routine - can you locate it in the repository through the Manager? Then a double-click on it will show the source code.

harshkohli · Post by **harshkohli** » Fri Jan 04, 2013 10:52 am

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.

chulett · Post by **chulett** » Fri Jan 04, 2013 11:13 am

Ah... so one of the SDK transforms. Can you now locate the second function / transform?

harshkohli · Post by **harshkohli** » Fri Jan 04, 2013 11:22 am

'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

chulett · Post by **chulett** » Sat Jan 05, 2013 9:00 am

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?

ArndW · Post by **ArndW** » Sat Jan 05, 2013 10:43 am

It would seem that

Code: Select all

ICONV('20121231','D')

will correctly convert, while

Code: Select all

ICONV('20130102','D')

won't.

chulett · Post by **chulett** » Sat Jan 05, 2013 11:03 am

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.

ArndW · Post by **ArndW** » Sat Jan 05, 2013 11:24 am

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.

chulett · Post by **chulett** » Sat Jan 05, 2013 11:46 am

Yah, I don't believe so either.

harshkohli · Post by **harshkohli** » Mon Jan 07, 2013 7:54 am

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.

chulett · Post by **chulett** » Mon Jan 07, 2013 8:15 am

Good find! Let us know...

ray.wurlod · Post by **ray.wurlod** » Mon Jan 07, 2013 3:44 pm

So, 2013 does not exist for US-English locale. Did the fiscal cliff thing actually happen?

chulett · Post by **chulett** » Tue Jan 08, 2013 7:35 am

Well, apparently the temporal cliff did.