1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

how to insert date in oracle

Discussion in 'Databases' started by alanX, Jul 17, 2006.

Thread Status:
Not open for further replies.
0
  1. #1
    Hi all,
    i want to insert Date as following:

    insert into db
    values('12/03/2006');

    Error: Not a valid month

    can anyone help me? (oracle 10g)
     
    alanX, Jul 17, 2006 IP
  2. hansi

    hansi Peon

    Messages:
    129
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    try

    YYYY-MM-DD
     
    hansi, Jul 18, 2006 IP
  3. alanX

    alanX Peon

    Messages:
    61
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I tried it but i still fail.

    now i am to_date function :
    Insert into db values(to_date('" + ReportDate.Text + "','dd/mm/yyyy'));

    The Date will be inserted into database successfully with the inputed format "12/03/2007".

    However, the output format was not one i expected.
    Output format: 12/3/2007 0:00:00

    How to delete those unwanted string "0:00:00"
     
    alanX, Jul 22, 2006 IP
  4. vrsane

    vrsane Active Member

    Messages:
    465
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    60
    #4
    create date in the database as varchar2
     
    vrsane, Jul 23, 2006 IP
  5. mopotofu

    mopotofu Peon

    Messages:
    164
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    to remove unwanted string which represents time
    you can do a "trunc(<date_column>)" when displaying the date column.
    also, doing a "to_char(<date_column>,'dd/mm/yyyy')" will work.
     
    mopotofu, Jul 23, 2006 IP
  6. drewbe121212

    drewbe121212 Well-Known Member

    Messages:
    733
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    125
    #6
    Exactly. Use TO_CHAR to do this.
     
    drewbe121212, Aug 1, 2006 IP
  7. svajdlenka

    svajdlenka Well-Known Member

    Messages:
    404
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    160
    #7
    try
    insert into db values(TO_DATE('12.03.2006', 'DD.MM.YYYY');
     
    svajdlenka, Aug 3, 2006 IP
  8. jkapadia1983

    jkapadia1983 Peon

    Messages:
    40
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    The default format for date in oracle is DD-MON-RR.

    so you can write insert statement as following

    insert into table (bdate) values ('18-JUN-84');

    column name is bdate and value is '18-JUN-84'
     
    jkapadia1983, Sep 30, 2009 IP
  9. DaveUK

    DaveUK Guest

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    use TO_CHAR function and convert your date format as you want (as long as it is valid format):
    example:

    select to_char(sysdate,'dd/Mon/yyyy') from dual - to display date in DD/Mon/YYYY format. No time displayed.

    select to_char(sysdate,'dd-Mon-yyyy') from dual - to display date in DD-Mon-YYYY format. No time displayed.

    select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') from dual - will display date and time in hh24:mi:ss format.
     
    DaveUK, Oct 8, 2009 IP
  10. rajpk

    rajpk Well-Known Member

    Messages:
    252
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    113
    #10
    take help from some online tutorial. ask it from the author of that tutorial

    it may solve your problem
     
    rajpk, Oct 18, 2009 IP
  11. Chicken Run

    Chicken Run Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    You should use keyword for your question
     
    Chicken Run, Oct 18, 2009 IP
  12. dgs02

    dgs02 Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    This is what you want in the database:
    insert into db values('12/03/2006');

    Here is what you need to do:
    Method 1:
    insert into db (target_column_name) values (to_date('12/03/2006','mm/dd/yyyy'));

    Method 2:
    insert into db values (to_date('12/03/2006','mm/dd/yyyy'));

    Method 1 is the preferred way because the target column is named. Target columns should always be named. Inserting into a table without naming the target columns is a bug waiting to happen.
     
    dgs02, Oct 29, 2009 IP
Thread Status:
Not open for further replies.