Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2011
    Posts
    10

    Exclamation Unanswered: Null ? TimestampDiff value

    I have a query that runs fine:

    Code:
    Select LPEM As EMPNAME,  SUM(LPQTY) As CASES, SUM(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) 	- TIMESTAMP(CHAR(LPSDT))	))) as JobTimeMinutes, SUM((timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) 	- TIMESTAMP(CHAR(LPSDT))	))  -  ((timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) 	- TIMESTAMP(CHAR(LPSDT))	)))*60)   )) as JobTimeSeconds      from KWABSPRDF.WEMPLPT where  LPSDT >=" + startTime + " AND  LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0   GROUP BY LPEM) as final  ORDER BY EMPNAME
    But now I want to filter on the results where JobTimeSeconds and JobTimeMinutes are NULL or 0? I am a sql server guy so I have tried sql server things. I looked on the IBM site and I have tried coalesce and IFNULL and float but both gave me errors:

    This is what I have now and it returns errors:

    Code:
    SELECT * FROM (Select LPEM As EMPNAME,  SUM(LPQTY) As CASES, SUM(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) 	- TIMESTAMP(CHAR(LPSDT))	))) as JobTimeMinutes, SUM((timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) 	- TIMESTAMP(CHAR(LPSDT))	))  -  ((timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) 	- TIMESTAMP(CHAR(LPSDT))	)))*60)   )) as JobTimeSeconds      from KWABSPRDF.WEMPLPT where  LPSDT >=" + startTime + " AND  LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0   GROUP BY LPEM) as final WHERE IFNULL(JobTimeMinutes,0) <> 0 AND IFNULL(JobTimeSeconds,0) <> 0  ORDER BY EMPNAME

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    What datatype are LPFDT and LPSDT ?

  3. #3
    Join Date
    Feb 2011
    Posts
    10
    Quote Originally Posted by Stealth_DBA View Post
    What datatype are LPFDT and LPSDT ?
    I think the problem is a 181 Timestamp overflow problem and not a null issue. Do you know how to solve this?

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The reason I asked is DB2 has much better Date/time function than SQL Server. If those columns are Timestamps, your query could be greatly simplified (which may make it easier to determine your current problem).

  5. #5
    Join Date
    Feb 2011
    Posts
    10

    I agree

    Quote Originally Posted by Stealth_DBA View Post
    The reason I asked is DB2 has much better Date/time function than SQL Server. If those columns are Timestamps, your query could be greatly simplified (which may make it easier to determine your current problem).
    I agree that the query can be simplified greatly. I am just trying to understand the behavior. I am writing a c# app that is getting data from an as400 via oledbconnection. The calculation is fine but when the data comes over it is empty/null/overflow. I could just do a linq query on the datatable but I want to understand what is happening. Basically what I want to accomplish is filtering off those values. It should be easier.

  6. #6
    Join Date
    Feb 2011
    Posts
    10

    Datatypes

    Quote Originally Posted by Stealth_DBA View Post
    The reason I asked is DB2 has much better Date/time function than SQL Server. If those columns are Timestamps, your query could be greatly simplified (which may make it easier to determine your current problem).
    They are timestamps.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    If your error is a -181 then you have an invalid date/time format. At a guess one or both of the timestamp columns may allow a NULL. It that is true, then you may be trying to do data arithmetic with a NULL which will get you a -181. If it is true, then try to filter them out in the WHERE clause (WHERE LPFDT IS NOT NULL AND LPSDT IS NOT NULL).

  8. #8
    Join Date
    Feb 2011
    Posts
    10

    I will try

    Quote Originally Posted by Stealth_DBA View Post
    If your error is a -181 then you have an invalid date/time format. At a guess one or both of the timestamp columns may allow a NULL. It that is true, then you may be trying to do data arithmetic with a NULL which will get you a -181. If it is true, then try to filter them out in the WHERE clause (WHERE LPFDT IS NOT NULL AND LPSDT IS NOT NULL).
    Thank you for the response

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Also here is what I meant by simplifying. When you subtract dates you get a date duration. You can use functions to extract out what you need. For example

    MINUTE(TIMESTAMP1 - TIMESTAMP) will give you the number of minutes (up to 59) difference.
    SECOND(TIMESTAMP1 - TIMESTAMP) will give you the number of seconds (up to 59) difference.

    If you have one or more hours you would need to extract HOURs and multiple by 60 and add it to the minute value to get over 60 minutes displayed as minutes.

    (HOUR(TIMESTAMP1 - TIMESTAMP) * 60 ) + MINUTE(TIMESTAMP1 - TIMESTAMP) AS MIN_DIFF,
    MINUTE(TIMESTAMP1 - TIMESTAMP) AS SEC_DIFF

    Granted, if your differences are over 1 day, this can start to get as complex as the original query.

  10. #10
    Join Date
    Feb 2011
    Posts
    10
    Quote Originally Posted by Stealth_DBA View Post
    Also here is what I meant by simplifying. When you subtract dates you get a date duration. You can use functions to extract out what you need. For example

    MINUTE(TIMESTAMP1 - TIMESTAMP) will give you the number of minutes (up to 59) difference.
    SECOND(TIMESTAMP1 - TIMESTAMP) will give you the number of seconds (up to 59) difference.

    If you have one or more hours you would need to extract HOURs and multiple by 60 and add it to the minute value to get over 60 minutes displayed as minutes.

    (HOUR(TIMESTAMP1 - TIMESTAMP) * 60 ) + MINUTE(TIMESTAMP1 - TIMESTAMP) AS MIN_DIFF,
    MINUTE(TIMESTAMP1 - TIMESTAMP) AS SEC_DIFF

    Granted, if your differences are over 1 day, this can start to get as complex as the original query.
    Linq meets my needs. I greatly appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •