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.

14JUN2000 is not a valid date representation. 2000-06-14 or 14.6.200 or 06/14/2000 would be.

I tried all these combinations but I get the same error.

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.

Thanks, the time stamp value worked.

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)

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:

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)

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!!