There is a manual page on Time and Date functions.
The best way to compare date and time and get minutes back is probably to use one of the following:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)
Note that while TIMEDIFF
works with times, TIMESTAMPDIFF
requires a date component - and the values are switched around for TIMESTAMPDIFF
.
You can return hours with either of these:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)
Note that when using DATEDIFF
, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).
To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:
SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)
This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.
According to IBM SQL3227W states:
"Record token token1 refers to user record number token2.
Explanation
An error or warning was encountered during LOAD, IMPORT or EXPORT of a
table. CPU parallelism was greater than 1 at the time the problem was
encountered, and an SQL message was written which identified the user
record with a special unique token. This message serves to map the
unique record token to the record number of the source user data.
User response
Refer to the original SQL message returned for appropriate action."
So this refers you back to the previous error. And the next one SQL3185W says:
"The previous error occurred while processing data from row row-number
of the input file.
Explanation
This message provides the identification of the row where the error
occurred for the previous message listed in the message file (for
example, SQL3306).
User response
No action is required."
So...Phil nailed it on the head, we really need to see what comes before these messages. Do you have a log of the output that you could post to your question? We could better help you with interpreting results then.
That being said, I would highly encourage you to poke around Information Center like I did as it can best help you find what those pesky "SQL" messages mean. The links I found were for 9.5. Just switch to the version of DB2 that you are using to make sure you have the most accurate information. I use Information Center for just about everything.
EDIT: Your updated helps a lot. Here is the error:
SQL3116W The field value in row "F12-33856" and column "9" is missing, but
the target column is not nullable.
SQL3185W The previous error occurred while processing data from row
"F12-33856" of the input file.
SQL3227W Record token "F12-33856" refers to user record number "440439".**
In short, it looks like a null was written to "column 9" of our table
and that you have a not null constraint on that column. Now the trick
is locating which record had the issue. It is marked with two tokens
it looks like. (F12-33856 and 440439). I guess I'd start with 440439
first. That seems to me like it could be the primary key. Just a
guess. But at least you know your issue is a null value for a non-null
field.
Best Answer
That should work. I created a file like:
and a table like:
and loaded like:
Could it be the reason that you did not quote the " so that the shell got rid of those?
If I do:
I get the error:
If that is not the problem, can you provide a small sample of rows that fail during load?