Date Error

kurtangle13 · #1 (**permalink**) 05-20-11, 13:26

Hi, I'm trying to subset a table in a query that is connected to a DB2 database.
DATE('20' || SUBSTR(TIME_FROM_TABLE,3,5)) is a function I have that produces a date
the substr take a 5 digit number from TIME_FROM_TABLE and represents the year and number of days since jan 1 of the year. so if the substr is 11001 then the date represented would be 01/01/2011

The function works properly and the date can be displayed, but now I want to use the date to subset so I only get rows where the date is after a certain time. However when I do this, I get the following error "The string representation of a datetime value is out of range SQLSTATE = 22007". I tried searching this up but there does not seem to be anything wrong with my sql.

Anyone knows how to solve this problem? Thanks a lot

n_i · #2 (**permalink**) 05-20-11, 14:00

Is there a reason why you chose not to show the actual SQL statement that fails? Is it some sort of a charade game where we are supposed to guess what's showing on your screen? It's a Friday thing, innit?

tonkuma · #3 (**permalink**) 05-20-11, 14:03

Quote:

... so I only get rows where the date is after a certain time. However when I do this, ...

You did successfully "DATE('20' || SUBSTR(TIME_FROM_TABLE,3,5))".
What did you do concretely by this?
What was the difference from the first time?

kurtangle13 · #4 (**permalink**) 05-20-11, 14:04

Quote:

Originally Posted by n_i

Is there a reason why you chose not to show the actual SQL statement that fails? Is it some sort of a charade game where we are supposed to guess what's showing on your screen? It's a Friday thing, innit?

The actually code is quite long so I thought it'd be easier this way.

kurtangle13 · #5 (**permalink**) 05-20-11, 14:07

Quote:

Originally Posted by tonkuma

You did successfully "DATE('20' || SUBSTR(TIME_FROM_TABLE,3,5))".
What did you do concretely by this?
What was the difference from the first time?

what I mean is this:

SELECT DATE('20' || SUBSTR(TIME_FROM_TABLE,3,5)) as NEW_DATE
FROM ...... BUNCH OF JOINS

works and I can see this column in the query result,

but when I do the following

SELECT *
FROM ....
WHERE DATE('20' || SUBSTR(TIME_FROM_TABLE,3,5)) >= '2000-01-01'

I get that error mentioned.

I've used this before where I have had a date column in a table and
column > '2000-01-01' worked
Is there something I'm missing here?

Thanks for all the help

n_i · #6 (**permalink**) 05-20-11, 14:16

Quote:

Originally Posted by kurtangle13

SELECT DATE('20' || SUBSTR(TIME_FROM_TABLE,3,5)) as NEW_DATE
FROM ...... BUNCH OF JOINS

works and I can see this column in the query result,

I highly doubt that.

tonkuma · #7 (**permalink**) 05-20-11, 14:19

This worked.

Code:

------------------------------ Commands Entered ------------------------------
VALUES CASE WHEN DATE('2011001') >= '2000-01-01' THEN 'Y' ELSE 'N' END;
------------------------------------------------------------------------------

1
-
Y

  1 record(s) selected.

So, the issue might be in another place.
Or analyze the values of TIME_FROM_TABLE.
For example:
SUBSTR(TIME_FROM_TABLE,5,3) must be lower than 365 or 366
depending on the value of SUBSTR(TIME_FROM_TABLE,3,2).

kurtangle13 · #8 (**permalink**) 05-20-11, 15:20

Quote:

Originally Posted by n_i

I highly doubt that.

Sorry to disappoint, but that statement does work, and I can see a date formatted column as the query result, which is why I am puzzled why the latter wouldn't work.

kurtangle13 · #9 (**permalink**) 05-20-11, 15:21

Quote:

Originally Posted by tonkuma

This worked.

Code:

------------------------------ Commands Entered ------------------------------
VALUES CASE WHEN DATE('2011001') >= '2000-01-01' THEN 'Y' ELSE 'N' END;
------------------------------------------------------------------------------

1
-
Y

  1 record(s) selected.

So, the issue might be in another place.
Or analyze the values of TIME_FROM_TABLE.
For example:
SUBSTR(TIME_FROM_TABLE,5,3) must be lower than 365 or 366
depending on the value of SUBSTR(TIME_FROM_TABLE,3,2).

For example, my string might be 11010010000 and my expression would take the substring 01001 and append 20 infront to get 2001001 and
DATE('2001001') gives me 2001-01-01

I see this perfectly in the query result. There is no error here
but it would not work when I try to use it in a where statement

tonkuma · #10 (**permalink**) 05-20-11, 16:42

Quote:

The actually code is quite long ...

How long are the code?
Less than 100 lines of SQL are not long for me.
"quite long" for me may be more than 500 lines or 1000 lines of query.

Actually, complexity of query is not related so much to the length of the query.
complexity of a query (for me) is related to the number of tables used, depth of nesting of subqueries, depth of nesting of functions/expressions, so on.

Please show whole code of a successful query and a failed query, and all text of error messages for the failing query

kurtangle13 · #11 (**permalink**) 05-20-11, 18:02

Quote:

Originally Posted by tonkuma

How long are the code?
Less than 100 lines of SQL are not long for me.
"quite long" for me may be more than 500 lines or 1000 lines of query.

Actually, complexity of query is not related so much to the length of the query.
complexity of a query (for me) is related to the number of tables used, depth of nesting of subqueries, depth of nesting of functions/expressions, so on.

Please show whole code of a successful query and a failed query, and all text of error messages for the failing query

Kay I will do so when I get in work on Monday.

Thanks

Stealth_DBA · #12 (**permalink**) 05-22-11, 22:56

kurtangle13, This recursive SQL generates all 365 days for 2011 to get your Year and Julian day of year values. All of those values work in the Where clause as in your example

Code:

WITH TESTTAB (SEQ) 
  AS (
      SELECT 11001 
      FROM SYSIBM.SYSDUMMY1 
        UNION ALL 
      SELECT SEQ + 1 
      FROM TESTTAB 
      WHERE SEQ < 11365
     )
SELECT DATE('20' || CAST(SEQ AS CHAR(5))) as DATE_VAL
     , SEQ
FROM TESTTAB
WHERE  DATE('20' || CAST(SEQ AS CHAR(5))) >= '2000-01-01'
;

DATE_VAL   SEQ        
---------- -----------
01/01/2011       11001
01/02/2011       11002
01/03/2011       11003
(removed to limit display)
12/30/2011       11364
12/31/2011       11365

  365 record(s) selected.

However if you change the starting value to 11000 (SELECT 11000) and/or the ending value to 366 or greater (WHERE SEQ < 11366), you will get the error message:

SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007

This leads me to believe your data is incorrect. What is extracted with the SUBSTR is some value that has the day out of range 0 or 366 (for non-Leap Year years)

Try this:

Code:

SELECT SUBSTR(TIME_FROM_TABLE,3,2) AS YY
     , MIN(SUBSTR(TIME_FROM_TABLE,5,3)) AS MIN_DDD
     , MAX(SUBSTR(TIME_FROM_TABLE,5,3)) AS MAX_DDD
FROM your-table-name
GROUP BY SUBSTR(TIME_FROM_TABLE,3,2)

This should give you the Min and Max day values for every year in your table. Look for out of range values.

Internet Services

Web Development

Digital Marketing

Consumer Tech