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

Question

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.

what you need is DateFormatter (http://stackoverflow.com/questions/5580965/java-date-formatter) — senseiwu, Feb 11 '16 at 14:57
`SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");`. — SatyaTNV, Feb 11 '16 at 14:57
Since you are getting it as String, use the [TO_CHAR](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm) function. Or get it as date and [format it](http://stackoverflow.com/questions/4772425/change-date-format-in-a-java-string) — BackSlash, Feb 11 '16 at 14:58

EdgeCase · Accepted Answer · 2016-02-11T15:57:44.743

2

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+"'";

edited Feb 11 '16 at 15:57

answered Feb 11 '16 at 14:59

EdgeCase

4,719
16
45
73

Please see Володин Андрей's comments below. He included an alias for the column, which I ommitted. They are a good idea with JDBC, and I updated by answer. – EdgeCase Feb 11 '16 at 15:58

score 0 · Answer 2 · answered Feb 11 '16 at 15:09

0

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

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

answered Feb 11 '16 at 15:09

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

410
7
11

Thank you Володин Андрей :) – Code Chest Feb 12 '16 at 10:18

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

2 Answers2