Link to home
Start Free TrialLog in
Avatar of mytilig
mytilig

asked on

Inserting todays date and time into Oracle

Executing the stmt
insert into table to_date(sysdate)
through a java code inserts only the date and not the time.

But if i execute the same query on sqlplus, it does insert the time. why is this so
and how do i insert the date and time thru java.
Thanks.


Avatar of petmagdy
petmagdy
Flag of Canada image

java.sql.Date date = new Date();
and use prepared statment
"insert into table values(date=?,....."

pstmnt.setDate(1,date);


Avatar of bloodredsun
SimpleDateFormatter will do this for you
---------------------

private String format = "yyyy-MM-dd HH:mm:ss";

public DateSimpleDate(String date,String timeZone) throws ParseException{
   
   cal = Calendar.getInstance();
   cal.setTimeZone(TimeZone.getTimeZone(timeZone));
   
   SimpleDateFormat formatter = new SimpleDateFormat(format);
   formatter.getCalendar().setTimeZone(TimeZone.getTimeZone(timeZone));
     
   return formatter.parse(date);
}
-----------------------------
pstmnt.setTimestamp(1, new java.util.Date().getTime());
Or

pstmnt.setTimestamp(1, System.currentTimeMillis());
Avatar of mytilig
mytilig

ASKER

final String qry ="insert into log values ('" +UserName + " ',' "+ ip      + "',"+ date +",'" +status+"')";
It says missing comma and if i put the date as 'date', it says it is not expecting a string there..
You should manipulate the insert with a PreparedStatement

final String qry ="insert into log values (?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(qry);

ps.setString(1, userName);
ps.setString(2, ip);
ps.setTimestamp(3, date);
ps.setString(4, status);

ps.executeUpdate();
It appears that the default date format of your Oracle database is set to only show the date and not the time.
if you are sole user AND you are certain it will not screw up something else you can change it.

but I would just remove that call to the to_date function. sysdate return a proper date value so there is no need to convert it to one:
    insert into table ( sysdate )

later when retrieving the value you wil need to specify the time-part as part of the desired datestring:
    select to_char(fieldname, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
from dual;


Avatar of mytilig

ASKER

CEHJ,

I get compilation error for these 2 stmts.

ps.setTimestamp(3, date);
ps.setString(4, status);

preparedstmt  is not applicable for Date and char.
(status is a char)


Jakoba,
If I run the above insert query directly on sqlplus, date and time does get entered.
Only when i run above query thru java, time is missing.
If i sqlplus and do select *, i see the ones entered thru java wihtout time and the ones manually entered with time.
ASKER CERTIFIED SOLUTION
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mytilig

ASKER

Thanks it works.
8-)