CODEWITHSUNDEEP
sqloracle11g
Alpha2k
Alpha2k

Reputation: 2241

Inserting date in database with hour and minutes

This is the method i use to insert a date into the database: 

public void insertLog(Connection con, UserLogs userLogs) throws SQLException{
    PreparedStatement pstmt = null;
    ResultSet rs= null;
    String SQL = "insert into logs values(?,to_date(sysdate,'DD/MM/YYYY HH24:MI'))";

    try {
        pstmt = con.prepareStatement(SQL);
        pstmt.setInt(1, userLogs.getUserId());
        rs = pstmt.executeQuery();
    } catch (SQLException e) {
        System.out.println(e);
    }finally{
        if(rs!=null){
            rs.close();
        }
        if(pstmt!=null){
            pstmt.close();
        } 
    }
}

Now there is a problem... it does successfully insert it but when i do an Select * from logs the hour and minutes appear as 00:00 (using oracle) so my guess is that i have to alter session with this format:

alter session set nls_date_format='DD/MM/YYYY HH24:MI';

in order for the display to work properly. How can i send this sql followed by an insert in the same executeQuery?

Upvotes: 0

Views: 1451

Answers (1)

Joe W
Joe W

Reputation: 1871

The issue is the insert statement you are using

SELECT to_date(sysdate,'DD/MM/YYYY HH24:MI') FROM dual;

returns 

9/24/0014

if you do 

SELECT sysdate FROM dual;

it will return

9/24/2014 14:56:54

There is no need to do the to_date on sysdate as it already includes the full date and time.

Upvotes: 1

Related Questions