CODEWITHSUNDEEP
oracle-databasedatetime
Buras
Buras

Reputation: 3099

Oracle. How to output date and time?

My Visit table is the following:

insert into Visit
values(12, to_date('19-JUN-13', 'dd-mon-yy'), to_date('19-JUN-13 12:00 A.M.' , 'dd-mon-yy hh:mi A.M.'));
insert into Visit
values(15, to_date('20-JUN-13', 'dd-mon-yy'), to_date('20-JUN-13 02:00 A.M.' , 'dd-mon-yy hh:mi A.M.'));
insert into Visit
values(18, to_date('21-JUN-13', 'dd-mon-yy'), to_date('21-JUN-13 10:30 A.M.' , 'dd-mon-yy hh:mi A.M.'));

When i try to query it: select * from Visit i get:

SQL> select * from visit;

   SLOTNUM DATEVISIT ACTUALARRIVALTIME                                                                                                                                                                                                                                                                                                                                                                                 
---------- --------- ------------------------------                                                                                                                                                                                                                                                                                                                                                                     
        12 19-JUN-13 19-JUN-13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        15 20-JUN-13 20-JUN-13                                                                                                                                                                                                       
        18 21-JUN-13 21-JUN-13                                                                                                                                                                                                      

SQL> spool off;

How come the time is not there?

Upvotes: 20

Views: 112096

Answers (5)

Ed Gibbs
Ed Gibbs

Reputation: 26363

An Oracle DATE column stores datetime values accurate to the second. Use TO_CHAR to format the dates:

SELECT
  SLOTNUM,
  TO_CHAR(DATEVISIT, 'MM/DD/YYYY') AS VisitDisp
  TO_CHAR(ACTUALARRIVALTIME, 'MM/DD/YY HH:MI:SS A.M.') AS ArrDisp
FROM Visit

The A.M. format code yields A.M. or P.M. depending on the time. You can use P.M. as well -- either works.

When inserting date values in script, IMO it's cleaner to use DATE and TIMESTAMP literals:

INSERT INTO Visit VALUES (
  12,
  DATE '2013-06-19',
  TIMESTAMP '2013-06-19 00:00:00');

INSERT INTO Visit VALUES (
  12,
  DATE '2013-06-20',
  TIMESTAMP '2013-06-20 02:00:00');

INSERT INTO Visit VALUES (
  12,
  DATE '2013-06-21',
  TIMESTAMP '2013-06-21 10:30:00');

For a "PM" time just use the 24-hour clock:

-- Time for the third column is 4:25 PM
INSERT INTO Visit VALUES (
  12,
  DATE '2013-06-22',
  TIMESTAMP '2013-06-22 16:25:00');

You can also set a format that applies to all dates like this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS PM';

That way, your original query would output the dates in the format you're after, without using TO_CHAR. To set back to the usual default format, just do this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

Upvotes: 37

Prasanth Pennepalli
Prasanth Pennepalli

Reputation: 1058

select localtimestamp from dual;

Upvotes: 0

Bhargav B
Bhargav B

Reputation: 31

Oracle internally follow 'DD-Mon-YY' format to store in database. So it returns'DD-Mon-Y

If you want to date format with hours min and sec. you can alter NLS_DATE_FORMAT in session.

If you want to query for the just Presentation purpose for that instance. Use TO_char Function to convert into required format.

SELECT slotnum, TO_CHAR (datevisit, 'DD-MON-YY ') "DATEVISIT",
       TO_CHAR (actualarrivaltime, 'DD-MON-YY HH:MI:SS AM') "ACTUALARRIVALTIME"
  FROM visit

I Hope the above query gives you the output as you like.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191560

You're relying on implicit date conversion, which is using your (session-dependent, but maybe inherited from the DB default) NLS_DATE_FORMAT setting - in this case that seems to be DD-MON-RR.

You can use to_char to specify the format, e.g.:

select to_char(actualarrivaltime, 'DD-MON-YYYY HH:M:SS PM') from ..

The datetime format models are described in the documentation.

In general it's better to never rely on implcit conversions. Even if you get what you expect now, they can be session-specific so another user in another client ,ight see something different. This can cause failures as well as just look wrong. Always specify date and number formats, using to_date, to_char, to_timestamp etc.

Upvotes: 2

Randy
Randy

Reputation: 16673

that is the oracle date format that is set as the default for your instance.

you should properly specify the format to see more or less.. something like this:

select to_char( datevisit, 'dd-mon-yy hh24:mi:ss' ) from visit

Upvotes: 26

Related Questions