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

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'))
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
0
slightwv (䄆 Netminder) 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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor 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);
0
Determine the Perfect Price for Your IT Services

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

slightwv (䄆 Netminder) 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?
0
brgdotnetcontractorAuthor 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.
0
slightwv (䄆 Netminder) 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;
0
DavidSenior Oracle Database AdministratorCommented:
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.
0
slightwv (䄆 Netminder) 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.
0
johnsoneSenior Oracle DBACommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.