oracle 10g中where子句中的年份和月份

Question

I have my date in format March 2014. How do I get month and year in number format to compare it in Oracle 10g query.

    <?php
    $date = "March 2014";
    ?>

My query that I am trying to get is

    select EMP_NAME where month = "03" and year = "2014".

Here is my table structure

    EMP_ID  |  EMP_NAME  |  C_DATE
       1    |     ABC    |  01-FEB-14
       2    |     XYZ    |  03-MAR-14 

Note: I am writing the coding in php

Answer 1

You can try with TO_CHAR(date, 'MON') for the months and TO_CHAR(date, 'YYYY') for the year. The query will be

select EMP_NAME where TO_CHAR(C_DATE, 'MON') = month and TO_CHAR(C_DATE, 'YYYY') = year

And in php you'll have to manage the date to get year and month and pass them to the query

$dt = new DateTime("March 2014");
$month = $dt->format("m"); //03
$year = $dt->format("Y"); //2014

//Here connect to the db..
$db->query("SELECT EMP_NAME FROM yourTable WHERE TO_CHAR(C_DATE, 'MON') = :month AND TO_CHAR(C_DATE, 'YYYY') = :year");
$db->bindParam(":month", $month);
$db->bindParam(":year", $month);