sql question 2

[Guest_imported]

i have a date variable ex... mont = '1-july-2001'
i want to write sql to select date in this month
ex...(result that i want)

1-jul-2001
2-jul-2001
...
31-jul-2001

please help me.

 

[Dharambhuta]

select DATEFIELD
from TABLENAME
where to_char(DATEFIELD,'MON-YYYY')=TO_CHAR(SYSDATE,'MON-YYYY');

 

[Guest_imported]

but i don.t have table .i have one variable.
ex month='1-jul-2001'

i want to select all date in this month.
can you help me?

 

[Dharambhuta]

where do you want these dates to appear? IN LOV or in List Item, or somewhere else, I cant think of any solution which can achieve what you want In a single query. probably you may have to use PL/SQL block.
so where/why you want to have all the dates? I am asking so that if there could be another possible way to do what you want.

 

[dgo]

Try this :

select to_date('01-JAN-02','dd-MON-yy') + rownum - 1 day
from all_objects
where
rownum <= to_char(add_months(to_date('01-JAN-02','dd-MON-yy'),1)-1,'dd')

 

[Dharambhuta]

That's a very good tip,dgo. I suggest to add TRUNC in your tip to get all dates of the given month.

select TRUNC(to_date('01-JAN-02','dd-MON-yy'),'MON') + rownum - 1 day
from all_objects
where
rownum <= to_char(add_months(TRUNC(to_date('01-JAN-02','dd-MON-yy'),'MON'),1)-1,'dd')