Home » SQL & PL/SQL » SQL & PL/SQL » Date formatting on table creation
Date formatting on table creation [message #202728] Sat, 11 November 2006 07:52 Go to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
I have a table that cotains a coloumn "Date of birth"
I wanted to format it to accept dates in the form DD/MM/YYYY
its getting tedious using

to_date('13/05/1987', 'DD/MM/YYYY')

when inserting into the table every single time
is there anyway i could format the column on creation of the table?
or possibly one command that will format it to always accept this form, so when inserting values into the table i could just type ('john', '12/06/1987')

Thanks
Re: Date formatting on table creation [message #202733 is a reply to message #202728] Sat, 11 November 2006 08:30 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, column that contains date values should be declared as DATE (you've already done that, if I'm not wrong, but won't hurt to mention it once again).

It is possible to set default date format for the current session or the whole system using ALTER statement and modifying NLS_DATE_FORMAT parameter.

However, as long as it is cozy to use something like this for everyday testing purposes, I'd suggest you to use TO_DATE function if you want to be sure that string that you use to represent date is always interpreted as a date.

Here is an example:
SQL> create table test (dob date);

Table created.

SQL> alter session set nls_date_format = 'dd/mm/yyyy';

Session altered.

SQL> insert into test values ('30/11/2006');

1 row created.

SQL>
Re: Date formatting on table creation [message #202737 is a reply to message #202733] Sat, 11 November 2006 08:53 Go to previous messageGo to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
Thanks alot works fine, so am i right in assuming this only alters for the session? so say if i were to restart my computer, would i have to reenter

SQL> alter session set nls_date_format = 'dd/mm/yyyy';

each time i logged on?
Re: Date formatting on table creation [message #202738 is a reply to message #202737] Sat, 11 November 2006 09:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, you have 3 options:
  • issue ALTER SESSION statement every time you log on
  • set NLS_DATE_FORMAT in registry (on Windows)
  • write AFTER LOGON ON DATABASE trigger which will alter session (the way you've already seen)
Re: Date formatting on table creation [message #202739 is a reply to message #202737] Sat, 11 November 2006 09:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

each time i logged on?

Yes

and you can set for instance.
sql>alter system set nls_date_format = 'format' scope=spfile;

and
bounce ur database ( shutdown or startup) for effect.


hope this helps.
Mohammad Taj.
Re: Date formatting on table creation [message #202748 is a reply to message #202739] Sat, 11 November 2006 12:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you a developer or a DBA?
If yes, then do NOT alter your nls_settings but get used to typing TO_DATE.
It's okay for developers to be a little lazy, but this is too much. You HAVE to always keep in mind that you are working with dates, not strings.
Previous Topic: stopping duplication
Next Topic: To get the first day of the month
Goto Forum:
  


Current Time: Sat Dec 03 20:32:30 CST 2016

Total time taken to generate the page: 0.09481 seconds