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

brgdotnet
brgdotnet used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

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.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>because I am only getting the date and not the time.

How do you know?  When you select it, are you providing the correct format mask?

select to_char(datetime,'MM/DD/YYYY HH24:MI:SS') from MYTABLE;
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);
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>'Test', 09-MAR-17

That is the default date format mask Oracle uses.  You need to alter that or explicitly set the format mask with TO_CHAR.

Try this:
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
SELECT * FROM MYTABLE;
David VanZandtOracle Database Administrator III
Commented:
Teaching point:
SYSDATE is a function that always fetches the current date and time as a string.

Querying SYSDATE will display the string in the session's NLS_DATE_FORMAT mask. The common default is DD-MON-YYYY.

If you want to view the string in some mask other than NLS_DATE_FORMAT, one must pass that spec to the function when it's called. The order of the displayed data has many, many flavors.

SLIGHTWV asked you to provide the column value with the supplemental mask above; you replied without it. IMO that's the basis for the confusion.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>Querying SYSDATE will display the string in the session's NLS_DATE_FORMAT mask.

Small correction:  The default isn't YYYY but YY and you don't need NLS_DATE_FORMAT set.  The system default is DD-MON-YY if there is no other format mask applied.
johnsoneSenior Oracle DBA
Commented:
SYSDATE is a function that returns the current date and time as a DATE, not a string.  It is returned as a 7 byte internal format which isn't easily human readable.  When displayed, all DATE types are converted to a string.

Just like SYSTIMESTAMP, returns a TIMESTAMP, not a string.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial