how to select date value from oracle with only Month and Year as inputs?

I am trying to retrieve date value from oracle based on month and year only like OCT 2009 is the input, I want to display on my web page all the days for this month and year only ...

else if trim(cdate) <>"" then
            strQuery1 = "select rowid,Year, Name, Loca, cdate, Net_ty, eq_ty, working, ser_code, circuit_name, line_ref, no_fault from asset_hdsl where trunc(cdate,'Mon-YYYY')="
    开发者_StackOverflow中文版        strQuery1 = strQuery1 & "'" & to_date(cdate,'Mon-YYYY') & "'"
     response.write strQuery1

I am getting syntax errors in ASP with this query.

Please can anyone help me out.

you can convert to Month and Year combination also as follow--

Select * from MyTable where to_char(MyDate,'MON YYYY') = upper('&mon &year');

or if you want to check with between clouse then you have to do like below query

SELECT * from MyTable WHERE MyDate between trunc(to_date('&mon &year','MON YYYY'),'MON') and last_day(to_date('&mon &year','MON YYYY'));

In place of &mon &year you have to give your variable name.

@Miroprocessor you can't apply to_date ('31 '+input+.........

in dynamic nature query you will not always find for Oct month only and in oracle database this query will give error for say JUN or FEB month ...

you can test on this things oracle database

    WHERE
   enddate>=date '2009-10-01' and enddate<date '2009-11-01';

This should work for you if you don't want to bypass table index and do a full table scan.

(src : How to select rows for a specific month )

  to_char(trunc(to_date('2021-03','YYYY-MM'),'Mon'),'YYYY-Mon')

It show output of 2021-Aug

to_char(trunc(to_date('2021-03','YYYY-MM'),'MON'),'YYYY-MON')

It show output of 2021-AUG

to_char(trunc(to_date('2021-03','YYYY-MM'),'Month'),'YYYY-Month')

It show output of 2021-August