Null ? TimestampDiff value

wtjohnson · #1 (**permalink**) 03-04-11, 09:45

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

Stealth_DBA · #2 (**permalink**) 03-04-11, 12:53

What datatype are LPFDT and LPSDT ?

wtjohnson · #3 (**permalink**) 03-04-11, 12:55

Quote:

Originally Posted by Stealth_DBA

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?

Stealth_DBA · #4 (**permalink**) 03-04-11, 13:02

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).

wtjohnson · #5 (**permalink**) 03-04-11, 13:06

Quote:

Originally Posted by Stealth_DBA

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.

wtjohnson · #6 (**permalink**) 03-04-11, 13:07

Quote:

Originally Posted by Stealth_DBA

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.

Stealth_DBA · #7 (**permalink**) 03-04-11, 13:21

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).

wtjohnson · #8 (**permalink**) 03-04-11, 13:23

Quote:

Originally Posted by Stealth_DBA

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

Stealth_DBA · #9 (**permalink**) 03-04-11, 13:43

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.

wtjohnson · #10 (**permalink**) 03-04-11, 13:45

Quote:

Originally Posted by Stealth_DBA

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech