1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

to_date: I get always the same format

Discussion in 'SQL PL/SQL' started by monkey, Jun 6, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!!
    I write:
    Code (SQL):
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('10.01.1999.','dd.mm.yyyy.'));
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('10.01.1999.','mm.dd.y.'));
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('10.01.1999.','dd.mm.yy.'));
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('10.01.1999.','dd.mm.yyy.'));
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('10/01/1999.','dd/mm/yyyy.'));
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('10011999.','ddmmyyyy.'));
     
    but I always get the same format (some inserts are from before):
    Code (SQL):
                              BROTHERID BROTHERNAME BROTHERBIRTHDATE
    -------------------------------------- ----------- ----------------
                                         1 xx          01.01.90        
                                                       22.02.90        
                                                       10.01.99        
                                                       01.10.99        
                                                       10.01.99        
                                                       01.10.99        
                                                       10.01.99        
                                                       10.01.99        
                                                       10.01.99        
                                                       10.01.99        
                                                       10.01.99        
                                                       10.01.99        

     
    Please, what is the way that I change the format at the very beginning, without later using to_char?

    many thansk!!!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    In the SQL session, use:

    Code (SQL):
    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
    You can use any format as you have used above in your post to display in the format you need.
     
    monkey likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Monkey,

    You need to display only in that format or you need to insert in that format?
     
    monkey likes this.
  4. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Wanna DISPLAY in the other format, without using to_char
     
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    solved!
    MANY THANKS!!!!