I am new to Oracle, and I need to save date and time in an Oracle database.
I am using time stamp as datatype for row. But now my problem is it saves date and time in 12 hours format like this 17/11/2011 10:10:10 PM
.
But I need it in 24 hours format like 17/11/2011 22:10:10
. I didn't understand the results that Google search result provided. Can any one please help me by posting some code.
You can use TO_CHAR in a query to display a DATE in any particular format, but, as you said, that makes the column a string, not a DATE. Usually, that doesn't matter. ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';SELECT hiredate, ... FROM scott.
RR is an "input" format. it means if you enter to_date( '01-jan-40', 'dd-mon-rr' ) Oracle will slide around the date based on the current year. In 1999 and 2001 -- that would be the year 2040. As opposed to yy -- where the century is based on the current date.
The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
Oracle always stores timestamps (and dates) in a packed binary format that is not human readable. Formatting is done only when a timestamp (or a date) is converted to a string.
You can control the formatting of your output by coding an explicit to_char
. For example
SELECT to_char( your_timestamp_column, 'DD/MM/YYYY HH24:MI:SS' )
FROM your_table
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With