MVSFORUMS.com

[balaji.p.raman]

Hi,

I have an integer field in my table where I store the date in YYYYMMDD format. I have a requirement now to fetch the rows that falls on a particular year and particular month. The month field however is not mandatory.
I am thinking putting a query which uses BETWEEN YYYYMM01 and YYYYMM31.
I am curious to know is there any other way to achieve the same result.

Again, I couldnt find out whether this has been already posted.
Please forgive if already posted
_________________
Rgds,
Balaji

[shantharam_k]

Using BETWEEN is probably the best bet for you.

However, any idea on why the filed is defined as INT? There is no gain in terms of space as both use 4 bytes. And u need to carry the extra overhead of validating the data before insertion (for ex. you can insert a value of 20080230 into this INT fileld which would fail if it were a DATE). Also take caution before insertion of data so that leading zeroes in the MM and DD fields are not lost. Else you may end up with catastrophic results.

[seekaysk]

Balaji, you could use YEAR and MONTH scalar functions. However, they depreciate the performance of your query is it is too critical. otherwise, that should give you hte results.

select * from table where year(column1) = 2000 and month(column1) = 01
_________________
Thanks.

[CraigG]

[seekaysk]

oh, my apologies. i might have not read the complete req. now I get ti's an INT column.
_________________
Thanks.