Results 1 to 12 of 12

Thread: Date Error

  1. #1
    Join Date
    May 2011
    Posts
    8

    Unanswered: Date Error

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... 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?

  4. #4
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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.

  5. #5
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by tonkuma View Post
    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
    Last edited by kurtangle13; 05-20-11 at 15:14.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kurtangle13 View Post

    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.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).

  8. #8
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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.

  9. #9
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by tonkuma View Post
    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
    Last edited by kurtangle13; 05-20-11 at 16:30.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    Last edited by tonkuma; 05-20-11 at 17:51.

  11. #11
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by tonkuma View Post
    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

  12. #12
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

Posting Permissions

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