CODEWITHSUNDEEP
javajdbcoracle12c
Code Chest
Code Chest

Reputation: 13

JDBC - How to Retrieve only date without time (ORACLE 12c)

I'm trying to build a small console application that receives ID of an employee in a company and prints his/her details. Everything goes fine, but when it comes to date of appointment of employee, it gives the date along with time. Example: "2005-03-10 00:00:00.0"

What I'm trying to do here is, I just want "2005-03-10". How can that be done? Here's the code:

public static void main(String[] args) {

    Scanner read = new Scanner(System.in);
    try
    {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:sys/password@//localhost:1521:xe");
    Statement st = conn.createStatement();

    System.out.print("Enter the id of employee: ");
    String id = read.next();

    String sql = "SELECT E.EMP_NAME, D.DEPT_NAME, ES.ROLE, ES.DATE_OF_APPOINTMENT, S.SALARY "
            + "FROM EMPLOYEE E, DEPARTMENT D, DEPARTMENT_ROLE DR, EMPLOYEE_SERVICE ES, SALARY S "
            + "WHERE E.EMP_ID = ES.EMP_ID AND E.EMP_ID=S.EMP_ID AND D.DEPT_ID=DR.DEPT_ID AND DR.ROLE=ES.ROLE "
            + "AND E.EMP_ID='"+id+"'";

    ResultSet rs = st.executeQuery(sql);

    while(rs.next()){
        System.out.println("\n\nEmployee Name\t: "+rs.getString(1)+"\nDepartment\t: "+rs.getString(2)
                +"\nRole\t\t: "+rs.getString(3)+"\nAppointed On\t: "+rs.getString(4)+"\nSalary\t\t: "+rs.getString(5));
    }
    st.close();
    conn.close();
    read.close();
    }
    catch(SQLException e){
        e.printStackTrace();
    }
    catch(Exception se){
        se.printStackTrace();
    }
}

The output looks like this:

Enter the id of employee: CCT003

Employee Name : JOSEPH BROWN

Department : HUMAN RESOURCE

Role : EXECUTIVE RECRUITER

Appointed On : 2006-02-13 00:00:00.0

Salary : 118000

Please help. Thanks.

Upvotes: 1

Views: 447

Answers (2)

EdgeCase
EdgeCase

Reputation: 4827

Since you're retrieving the data as a string, you could just add one more step and format it

String sql = "SELECT E.EMP_NAME, D.DEPT_NAME, ES.ROLE, TO_CHAR(ES.DATE_OF_APPOINTMENT,'YYYY-MM-DD') AS DATE_OF_APPOINTMENT, S.SALARY "
            + "FROM EMPLOYEE E, DEPARTMENT D, DEPARTMENT_ROLE DR, EMPLOYEE_SERVICE ES, SALARY S "
            + "WHERE E.EMP_ID = ES.EMP_ID AND E.EMP_ID=S.EMP_ID AND D.DEPT_ID=DR.DEPT_ID AND DR.ROLE=ES.ROLE "
            + "AND E.EMP_ID='"+id+"'";

Upvotes: 2

Володин Андрей
Володин Андрей

Reputation: 440

You can modify SQL query and use Oracle TO_CHAR() function:

    TO_CHAR(ES.DATE_OF_APPOINTMENT, 'YYYY-MM-DD') as DATE_OF_APPOINTMENT

Upvotes: 0

Related Questions