We help IT Professionals succeed at work.

How to get the current date and Time upon oracle insert into a database table

brgdotnet
brgdotnet asked
on
5,161 Views
Last Modified: 2017-03-12
How can I get the current date AND time ? I have experimented, but no success, because I am only getting the date and not the time.
Can someone help me out?

(CURRENT_TIMESTAMP and or TO_DATE(sysdate,'dd/mm/yyyy hh:mi:ss')   )

This is what I tried :

INSERT INTO MYTABLE (TABLENAME, DATETIME) VALUES ('Test',CURRENT_TIMESTAMP)

INSERT INTO MYTABLE (TABLENAME, DATETIME) VALUES ('Test',TO_DATE(sysdate,'dd/mm/yyyy hh:mi:ss'))
Comment
Watch Question

johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Do no put a TO_DATE function on SYSDATE.  It is already a date, there is no need to covert it.  In fact, what is happening is this:

TO_DATE(TO_CHAR(SYSDATE))

Based on your default NLS_DATE_FORMAT you are probably losing the time in that conversion.

If the data type of the column in the database is TIMESTAMP, then use SYSTIMESTAMP.

If the data type of the column in the table is DATE, then use SYSDATE.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
brgdotnetcontractor

Author

Commented:
Based on your reply, I tried this, but it is not working? I am still getting only the date and not the time.

INSERT INTO MYTABLE (TABLENAME, DATETIME) VALUES ('Test',sysdate);
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I am still getting only the date and not the time.

Just in case you missed my post:
How do you know?  When you select it, are you providing the correct format mask?
brgdotnetcontractor

Author

Commented:
What do you mean sir? After I run the insert code, I then select everything from the table, and  I get the following : 09-MAR-17

SELECT * FROM MYTABLE

gives :

'Test', 09-MAR-17

Should my select be different, or should my insert sql statement be different? Thank you so much for working through this. I am just trying to understand.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
David VanZandtSr Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions