Results 1 to 9 of 9

Thread: filtering dates

  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: filtering dates

    Hi all,

    I am new to DB2, I have to access a DB2 database using SAS and I am running the following query


    select clmerrcrt,edibat,extclm,membno,provno,msgcod,errds c,formcd
    from clmerret
    where msgcod in ('MH02053','MH11622')and clmerrcrt ='14JUN2000';


    I am getting the following error:

    [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0180 - Syntax of
    date, time, or timestamp value not valid.


    I am not sure what the problem with the query is. If I remove the date filter, the query works fine but it retrieves lot of unwanted data.

    The dates are stored in the database such as 14JUN2000:00:00:00.000000.

    instead of 14JUN2000 I tried other combination of dates like 04252007, 4252007, etc. I am running out of ideas. Any help would be greatly appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    14JUN2000 is not a valid date representation. 2000-06-14 or 14.6.200 or 06/14/2000 would be.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2002
    Posts
    77
    I tried all these combinations but I get the same error.

  4. #4
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    run the following:
    describe select * from clmerret

    This will tell you the type for the clmerrcrt column. If it is a timestamp, try '2000-06-14-00.00.00.000000'. If it's a date, try just the first 10 characters. Be careful with the '-' and '.' characters. DB2 is very particular about them.

  5. #5
    Join Date
    Jan 2002
    Posts
    77
    Thanks, the time stamp value worked.

  6. #6
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by jsharon1248
    run the following:
    describe select * from clmerret
    This will tell you the type for the clmerrcrt column. If it is a timestamp, try '2000-06-14-00.00.00.000000'. If it's a date, try just the first 10 characters. Be careful with the '-' and '.' characters. DB2 is very particular about them.
    1) How can I change the type of a column?
    I use QMF for Windows, and when you run the query it shows under the
    records some boxes (one per field) with the name of the field written on each
    box. If you click on one box, you can change the format but (it seems to me) only within a limited range of types, that are: as is, decimal, ...and others. For example I don't find the type 'date'. Does it exist?
    When I change the type, the field appears changed, but the box still
    declare the original type: for example I changed decimal with six ciphres after
    the comma into 0 decimal, but the box continues showing data type decimal
    with 10 ciphres.
    Is there another way to change data type?

    2) With forms you can instruct query to group records, make the sum in a column of a group of records, and so on.
    Can I give these instructions directly in the query without having to repeat
    each instruction in the form each time I run the query?

    Thank you.

    Anna - Verona (Italy)

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Changing the type of a column can be done with the ALTER TABLE statement. But I'm not sure that this is what you have in mind...

    You can change the type of values in the result set of a query by applying a cast:
    Code:
    CREATE TABLE t ( a INT )
    
    SELECT CAST(a AS BIGINT) FROM t
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2007
    Posts
    51
    With forms you can instruct query to group records, make the sum in a column of a group of records, and so on.
    Can I give these instructions directly in the query without having to repeat
    each instruction in the form each time I run the query? Or can I save
    a form (with different instructions) for the query?

    Thank you.

    Anna - Verona (Italy)

  9. #9
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    There are several column functions that you would use to 'group' your results. You would need to include a 'group by' for all the columns in the select list that do not have a column function. For example, to sum an integer by date, you'd use something like this:

    select date_col,sum(int_col)
    from table_nm
    group by date_col

    You can mix and match the columns/functions. There's also a 'having' clause that's useful to further limit the results set. In the above example, if you're only interested in dates where the sum(int_col) is greater than 100, you'd modify the query to the following:

    select date_col,sum(int_col)
    from table_nm
    group by date_col
    having sum(int_col) > 100

    Enjoy!!

Posting Permissions

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