Store dates in oracle

Question

I have a table like

create table Dummy (date_created date)

in oracle.I want to save the date in the format 'dd-mon-yyyy' (12-dec-2010). How am I supposed to do this. Please, help.

Answer 1

In Oracle, a column created with a DATE data type simply saves the date. It does not have a specific format, it simply stores the day, month, year, hour, minute and second. You need to convert from any format you have using the TO_DATE function. If you have a text string with a date in the format "dd-mon-yyyy" and you want to put this date in your table, you should use something like

INSERT INTO DUMMY (DATE_CREATED)
  VALUES (TO_DATE('01-FEB-2011', 'DD-MON-YYYY');

( DATE ), TO_CHAR. "DD-MON-YYYY", -

SELECT TO_CHAR(DATE_CREATED, 'DD-MON-YYYY')
  FROM DUMMY;

.

Answer 2

to_date().

insert into Dummy values (to_date('08-09-2010', 'dd-mm-yyyy'));

.

Answer 3

DATE (, , , , , ) . , , .

TO_DATE ANSI date literal. v $nls_parameters, , NLS_DATE_FORMAT, . DD-MON-RR, , select sysdate from dual 23-DEC-10. ( , nls. , .)

, , . select to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual .

, insert, , .

insert into dummy (date_created) 
values (to_date('12-dec-2010 12:34:56', 'dd-mon-yyyy hh24:mi:ss'))`

.

select date_created from dummy

NLS_DATE_FORMAT, .

DATE_CREA
---------
23-DEC-10

.

select to_char(date_created, 'DD-MON-YYYY') as date_created from dummy;

select to_char(date_created, 'DD-MON-YYYY HH24:MI:SS') as date_created 
from dummy;

:

DATE_CREATE
-----------
23-DEC-2010

TO_DATE TO_CHAR / , . , .

Answer 4

.

http://www.techonthenet.com/oracle/functions/to_date.php

to_date . SYSDATE, , - :

dbms_output.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

:

to_date ('08/JAN/2010 ',' DD/MON/YYYY ')

Answer 5

TRUNC (YourDate), , . Oracle "DATE", "DATE" .

, - 00:00:00.

SELECT TRUNC(SYSDATE) from dual

:

23-12-2010

Answer 6

Oracle DATE .

, CHECK:

CREATE TABLE dummy (date_created date CHECK (date_created = TRUNC(date_created)))

:

INSERT
INTO    dummy (date_created)
VALUES  (TO_DATE('23-DEC-2010', 'dd-mon-yyyy'))

:

SELECT  TO_CHAR(date_created, 'dd-mon-yyyy')
FROM    dummy