Oracle_ Query Date shows only the date does not show time ____oracle

Inserts a time into the Oracle database, where the time is 00:00:00 why only the date displayed in the database does not show time after inserting.
Recommended Answer 2011-11-15 18:19
Displaying time requires converting a date column to a character type, using To_char (date, ' Yyyy-mm-dd hh24:mi:ss '), or modifying the Oracle default date format.
Ask
I mean, in the database, the query results in the Pl/sql only have a date and no time, but now you want to display the time in the datasheet.
Reply
Oracle has no datetime for this data type, only the date data type date contains the time, but the query does not show the time (in fact, the database is stored in time),
To see the time you need to convert with To_char, or change the Oracle default date format.


Quite simply, use the To_char () function to
Get Date: To_char (sysdate, ' yyyy-mm-dd ');
Acquisition Time: To_char (sysdate, ' hh24:mi:ss ');
To_date (' 2012-02-10 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

The Oracle date type defaults to show only dates, not time
Http://www.itkee.com/database/detail-4b.html
To use Oracle 10g in your work, you often insert the date and time that the event occurred to one of the tables. Write a note specifically on Oracle's date and time display and how it is inserted.
Like the built-in data types such as NUMBER,VARCHAR2, Oracle uses date, the built-in data type, to store dates and times. As with MS SQL Server, dates and times are stored in a data type, and there is no single time or date data type that stores only time or dates. The date data type stores the date and time of the month.


When displaying date-type data, Oracle first converts the stored value from an internally saved format to an output string. Typically, this transformation is done by to_char this function. If the To_char function is not used to specify a specific date-time format, the Oracle default display format is "Dd-mon-yy".


For example, we created a table x:
CREATE TABLE ABC (a int, b date);
Then we execute the following query statement:
Select b from ABC;
We will see the following result (set head on):
B
------------
01-apr-09
(language is English) or B
-------------
01-4 Months-09
(Language is Chinese)
That is, each time a numeric value of date type is displayed, Oracle automatically calls the To_char function and displays the format as a parameter in the default date. We can explicitly call the To_char function and specify the format that we want. For example:


SELECT To_char (b, ' Yyyy/mm/dd ') as b from ABC;


The result returned is:


B
------------
2010/09/01
To_char is a powerful function that converts the Date,mlslabel,number type of data into the VARCHAR2 type of data in the specified format, where we only care about the conversion of the date time. Grammar:


To_char (d [, fmt [, ' Nlsparams ']])


D is a date-type variable, and FMT is the datetime format we specify, if you do not explicitly specify the default value for Oracle. The usual date-time placeholders in FMT are as follows:


MM in numeric month (for example, 07)
MON abbreviated month name (for example, June)
MONTH Full month name (for example, July)
DD date (for example, 24)
DY the abbreviation of the week (e.g., FRI)
YYYY 4-bit year (for example, 2008)
YY in 2-bit year, take the latter two digits of the year (for example, 08)
RR is similar to YY, but the two-digit year is approximately 1950 to 2049 years in the range, for example 06 is considered 2006 instead of 1906
AM (or PM) on PM indicator
HH 12 time in the system (1-12)
HH24 24 The time of the binary representation (0-23)
MI minutes (0-59)
SS seconds (0-59)
The above is about using the To_char function to display date-type data, and here's how to insert a date-type value into the table. At this point we are going to use the To_date function to convert the date of the string representation to the type of date.


The To_char function is implicitly called when Oracle displays DateTime, and when Oracle expects a value of type date, it implicitly invokes the To_date function to convert the input string, depending on the default format "Dd-mon-yy".


Or take our X-tables for example, we can directly enter:


INSERT INTO ABC values ("31-may-08");
However, regardless of the format used in the display will not affect the data we actually store, and when inserting, simply using Oracle's default format is not so consistent with the job needs, we still need to explicitly call the To_date function, for example:


INSERT into ABC values (TO_DATE (' 2008/05/31:12:00:00am ', ' Yyyy/mm/dd:hh:mi:ssam '));
Syntax for to_date functions:


To_date (char [, FMT [, ' Nlsparams ']])


Char is a string that represents a date and time. The FMT representation method is the same as the To_char function.


We've mentioned earlier that Oracle's default date-time format is "Dd-mon-yy", in fact, we can modify this default format and change it to the format we need. Enter the following command in the Sql*plus:


Alter session set nls_date_format= ' <my_format> '; -This change is only useful for the current session.


For example:


Sql> alter session set nls_date_format= ' YYYY-MM-DD ';
The session has changed.
Sql> INSERT INTO ABC (b) VALUES (' 2004-08-26 ');
1 lines have been created.
The Oracle built in function sysdate can return the current date and time of the system, for example:
Select To_char (sysdate, ' Dy dd-mon-yyyy HH24:MI:SS ') as "current Time" from dual;