Re: TIMESTAMPDIFF Not Found

[Dennis <iseries@xxxxxxxxxxxx>]

http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html


"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx> wrote:

> Hi,
>
> Date format YYYYMMDD is not allowed with DB2 UDF for i.
>
> A character string representing a date in the format YYYY-MM-DD or
> MM/DD/YYYY or DD.MM.YYYY can be converted into a real date without
> specifying any date format:
> Date(MyDate)
>
> If you want to convert a numeric field (8,0 format YYYMMDD) into a real
> date
> using the following syntax is the easiest:
> Date(Digits(MyDate) concat '000000')
>
> BTW it seems to me your time is 6,0 format HHMMSS.
>
> To convert a numeric date (8,0) and numeric time (6,0) the easiest way
> is:
> Timestamp(Digits(MyDate) concat Digits(MyTime))
>
> Timestampdiff needs the difference between 2 timestamps defined as
> CHAR(22).
> Not sure what SQL_TSI_SECOND is, in either way it needs to be an
> integer
> with either value 1, 2, 4, 8, 16, 32, 64, 128 or 256.
>
> Timestampdiff(2, Cast(MyTimestamp1 - MyTimestamp2 as Char(22)))
>
> Hope this helps.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars."
> (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training
> them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Ryan
> Gesendet: Wednesday, 26. January 2011 14:22
> An: Midrange Systems Technical Discussion
> Betreff: TIMESTAMPDIFF Not Found
>
> I'm trying to debug a colleagues SQL statement, and when I put it into
> Visual Explain, I'm getting an error that TIMESTAMPDIFF can't be
> found. I'm also getting errors that the date format identifier
> (yyyymmdd) is not in any of the tables. Is there a format identifier
> on the date function? I'm playing around with the statement, removing
> and adding to see if I can get it to work. Here's the original SQL
> statement:
>
> SELECT  date( POSEVTP.EVDATE, yyyymmdd ) AS EVDATE,
> XMSTNO,
> XMINNO,
> XMINVT,
> POSEVTP.EVUSER,
> POSEVTP.EVBEFR,
> POSEVTP.EVAFTR,
> POSEVTP_1.EVAFTR AS EVAFTR_1,
> POSEVTP_1.EVUSER AS EVUSER_1,
> TIMESTAMP( date( POSEVTP.EVDATE, yyyymmdd ), substr( digits(
> POSEVTP.EVTIME ), 1, 2 ) || ':' || substr( digits( POSEVTP.EVTIME ),
> 3, 2 ) || ':' || substr( digits( POSEVTP.EVTIME ), 5, 2 ) ) AS
> COLUMN0003,
> TIMESTAMP( date( POSEVTP_1.EVDATE, yyyymmdd ), substr( digits(
> POSEVTP_1.EVTIME ), 1, 2 ) || ':' || substr( digits( POSEVTP_1.EVTIME
> ), 3, 2 ) || ':' || substr( digits( POSEVTP_1.EVTIME ), 5, 2 ) ) AS
> COLUMN0002,
> FLOAT( TIMESTAMPDIFF( SQL_TSI_SECOND, ( TIMESTAMP( date(
> POSEVTP_1.EVDATE, yyyymmdd ), substr( digits( POSEVTP_1.EVTIME ), 1, 2
> ) || ':' || substr( digits( POSEVTP_1.EVTIME ), 3, 2 ) || ':' ||
> substr( digits( POSEVTP_1.EVTIME ), 5, 2 ) ) ), ( TIMESTAMP( date(
> POSEVTP.EVDATE, yyyymmdd ), substr( digits( POSEVTP.EVTIME ), 1, 2 )
> || ':' || substr( digits( POSEVTP.EVTIME ), 3, 2 ) || ':' || substr(
> digits( POSEVTP.EVTIME ), 5, 2 ) ) ) ) ) / FLOAT( 60 ) AS COLUMN0005,
> XMRETL
> FROM
> NEWSYS.POSEVTP POSEVTP,  NEWSYS.POSMSTP POSMSTP,  NEWSYS.POSEVTP
> POSEVTP_1
> WHERE
> POSEVTP.EVTRAN=POSMSTP.XMTRAN AND
> POSEVTP.EVTRAN=POSEVTP_1.EVTRAN AND
> POSEVTP.EVAFTR=POSEVTP_1.EVBEFR AND
> ( POSEVTP_1.EVAFTR = 'I' AND POSEVTP.EVDATE = 20041219 )
>
> Any help appreciated. Thanks!
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.