datefiled in dd-mm-yyyy format

[indu iyengar]

Hi

how to create a table with field DOB in dd-mm-yyyy format?

so that I can insert date in dd-mm-yyyy format .

thanks
indu

[Agador Paloi]

I always use date or timestamp for a date - time table column.
To define your table would be something like this:

   
CREATE TABLE TEST_TABLE
 (test_SEQ     NUMBER(9,0) NOT NULL
 ,test_name   VARCHAR2(25)
 ,test_DOB     DATE        NOT NULL
 )

 

To put in a row would be :

  
SQL> INSERT INTO TEST_TABLE
  2   ( test_seq,test_name,test_dob)
  3   VALUES
  4   ( 1, 'JOE', to_date('30-08-1980', 'DD-MM-YYYY') )
  5  /

1 row created.

SQL>  

you use a mask in your to_date function of your sql to determine the date format you want.

[indu iyengar]

 INSERT INTO test VALUES(DATE_FORMAT('30.08.10','%d.%m.%y'));  

inserting finely .. but when I retrieve

 SELECT * FROM test   

the result is in yyyy-mm-yy format .
thanks
indu

[Rene Larsen]

A DATE field in a DB only 'holds' the date, in no specific format - you need to use DateFormat (SimpleDateFormat) to format your date to what you want afterwards.

[Agador Paloi]

or you can use a mask in your select

  

  SQL> SELECT
  2      test_seq,
  3      test_name,
  4      to_char( test_dob, 'DD-MM-YYYY' )
  5  FROM
  6    TEST_TABLE
  7  /

  TEST_SEQ TEST_NAME                 TO_CHAR(TE
---------- ------------------------- ----------
         1 JOE                       30-08-1980
         2 TED                       01-07-1979

SQL>
  

Agad

[Paul Campbell]

Oracle stores all DATE data types as a seven digit binary. The format mask you use on insert statement has nothing to do with how it is stored.

As Agador correctly states, you use to_char to convert your date to a string and choose your format mask for display.

If you don't explicitly do this yourself, Oracle will internally call a version of to_char to convert the binary data to a string using the default date format parameter stored in the nls_database_parameter table. Usually this is dd-mon-rr by default.