MVSFORUMS.com

[enge]

hello everyone,
i can't test a column of a db2 table using "CURRENT DATE".
for example:

table:
col1
----------
20091015
20091014
20091010
20090909

select count(*) from mytable
where col1 > CURRENT DATE

how can i do?
thanks in advance.

[dbzTHEdinosauer]

what is the datatype of col1. if it is a datetime datatype, then your sql is ok.

but, probably, col1 is packed-decimal or someother idiot datatype.
so, you have to cast current_date to be the same datatype as col1.

casting col1 to be a date would not be good idea, then db2 must cast every column.
but if you cast the current_date to whatever datatype col1 is, the conversion is only done once.
_________________
Dick Brenholtz
American living in Varel, Germany

[enge]

col1 is packed-decimal,unfortunately,and contains numeric values in yyyymmdd format.
so, can you suggest me how to change my sql select casting "CURRENT DATE"?

[Terry_Heinze]

If this is embedded SQL in a COBOL program, convert CURRENT DATE to yyyymmdd format then compare COL1 to the converted CURRENT DATE as Dick suggests.
_________________
....Terry

[NASCAR9]

enge, This may work:

[enge]

thank for your intervention terry but,
to be accurate .. is an unload from a db2 table such as:

[kolusu]

enge,

Try this sql. I assumed your date has - as the separator character.

[NASCAR9]

Why won't this work? In your first post the data does not contain dashes '-'

Edit: after re-reading the above post, my solution may be cumbersome.

[enge]

awesome,
both got the target.thank you very much