CODEWITHSUNDEEP
sqloracle-databasedatetime
DarrenJ65
DarrenJ65

Reputation: 25

DATE/TIME in oracle - date duration function

I am trying to create a function that can subtract between 2 dates but I am having some issues:

Firstly with the storing of the date, and secondly with the function.

Here is my code for storing the date in to a table:

CREATE TABLE car_info
  (
   car_id NUMBER(4)
   arrival DATE,
   departure DATE
   ......
  )

here I have saved the arrival/departure times as DATES. Ideally I will want it in the format of dd-mon-yyy-hh-mm-ss

This is where I encounter my first problem:

INSERT INTO car_info 
VALUES('0001', to_date('12-jun-2006 06:00:01'), to_date('14-jun-2006 09:00:01')

From the research I have done, this seems correct?

however if I do a SELECT * FROM car_info, this is the result:

car_id       arrival_date      departure_date
   1          12-jun-2006        14-jun-2006

Does anyone know why it is not displaying the result as a full date and time?

To stop this post getting too full of information, once I have this problem solved I will post my issues with the function.

Thanks for any help

kihd regards, Darren

Second part of question has been moved to Oracle sql - date subtraction within a function

Upvotes: 2

Views: 3132

Answers (3)

Ollie
Ollie

Reputation: 17558

Oracle stores dates in a binary internal representation so how you enter them and the format you retrieve them can be set either at the session, database or query level.

You should generally explicitly format your dates when inserting to prevent any implicit conversion errors:

INSERT INTO car_info  
VALUES('0001', 
       to_date('12-jun-2006 06:00:01'), 
       to_date('14-jun-2006 09:00:01'))

Should be:

INSERT INTO car_info  
VALUES('0001', 
       to_date('12-jun-2006 06:00:01', 'dd-mon-yyyy hh24:mi:ss'), 
       to_date('14-jun-2006 09:00:01', 'dd-mon-yyyy hh24:mi:ss'))

EDIT: The format models for Oracle dates etc. are here.

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

DATE in Oracle is a point in time, it is not stored with a format. A format is chosen when the date is displayed so that it can be understood by humans.

DATE by default will be displayed as per your NLS_DATE_FORMAT setting. You can change this setting with the following command:

ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';

You could also ask for an explicit format by using TO_CHAR in your query:

SELECT to_char(arrival, 'dd-mon-yyyy hh24:mi:ss')
  FROM car_info

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132660

A DATE column is held in the database encoded in 7 bytes, not as a formatted string. When you select a DATE column from the database the client tool you are using has to format it to display it in a meaningful way. It does this using a default format mask. How you set this depends on the tool you are using. In SQL Plus, you can do this:

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Or you can format the date yoursefl in the query using TO_CHAR.

Upvotes: 2

Related Questions