I am trying to run a select statement with a field in the where clause that I want to check the Month value of, but it is in a decimal field. The format that it currently is in is (YYYYMMDD). Any ideas on how to change the format of the field in the where clause? I need to be able to do it as follows:

select * from table1 where month(field1) = 10

value in field1 = 20041015

Thanks in advance for any help,
Jeremy

why dont you select all the records based on the where condition
then convert the variable to date. Check it, if it is what you want

continue
else
continue foreach
--get the next record

or something like that

hope this helps

Cheers

Artem

Artem,
Thanks for the quick reply. I will give that a try. I am new to the Informix world (as is obvious by some of my posts). I have been spoiled by some of the functions in SQL server, and I have also inherited a less than desireable data model. Guess we have to play with the hand that is dealt us.

Thanks,
Jeremy

Use substr() to retrive information as below:

select * from tabx where substr(lpad(num_dt_field,8,'0'),5,2)='10' ;

Thanks for the reply. I will give that a try.

Jeremy