Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Date Column Format

    Right now, when I insert a row containing a column with the date data type, I insert "05-Feb-2003" and all inserts well. When I look into the database with the "Table Data Editor" in "DBA Studio", I get "05-Feb-2003 12:00:00 AM". First off, the time is wrong so I want to make it where the column ONLY has "05-Feb-2003". Can someone help me out? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Date Column Format

    All Oracle DATE columns have a time component without exception. If no time is specified, this defaults to 00:00:00, i.e. 12:00:00AM. If you don't want to see the time component, you must change the display format: I don't know whether you can do that in DBA Studio, or how, but the fact is that it is a DBA Studio setting issue and NOT a data issue.

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I found the problem. Some how, the information being inserted into Oracle is being modified as the date should read:

    05-Feb-2003

    But it's reading:

    20-Feb-0005 03:00:00 AM

    Can you help? Thanks, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How is the data being inserted? If it is via INSERT statements then you should use the TO_DATE function with a specified format mask, like this:

    INSERT INTO tab (date1, ... ) VALUES (TO_DATE('05-FEB-2003','DD-MON-YYYY'), ...);

    Probably what is happening is this:

    INSERT INTO tab (date1, ... ) VALUES ('05-FEB-2003', ...);

    This makes Oracle do an implicit conversion from VARCHAR2 to DATE using the default date format mask. In your case, this appears to be something like 'YY-MON-DDHH', which would explain the garbled date.

    Alernatively, if you are inserting via a screen then it seems to expect you to enter dates backwards, e.g. 03-FEB-05 for 05 Feb 2003

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Ok, do you see anything wrong with this:

    Code:
    OraCon.Execute "INSERT INTO QUOTES (QUOTE_NUM,QUOTE_COMP,QUOTE_CREATOR," & _
        "QUOTE_MODIFIER,QUOTE_SELLER,QUOTE_BUYER,QUOTE_PO_NUM,QUOTE_PART_NUM," & _
        "QUOTE_PART_NAME,QUOTE_C_DATE,QUOTE_M_DATE,QUOTE_REV) VALUES ('" & txtQuoteNum.Text & "','" & _
        cmbCustomer.Text & "','" & txtEstimator.Text & "','" & txtEstimator.Text & "','" & _
        cmbSeller.Text & "','" & cmbBuyer.Text & "','" & txtPONum.Text & "','" & _
        txtPartNum.Text & "','" & txtPartName.Text & "',TO_DATE('" & Format(Now, "dd-mmm-yyyy") & "','dd-mmm-yyy'),TO_DATE('" & Format(Now, "dd-mmm-yyyy") & "','dd-mmm-yyyy'),'" & txtRevision.Text & "')"
    ? I'm getting an invalid date format during the insert. Thanks alot, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    TO_DATE('" & Format(Now, "dd-mmm-yyyy") & "','dd-mmm-yyy')
    I'm not familiar with the Format function, but I presume it returns '05-FEB-2003' for today's date?

    The format mask for TO_DATE is wrong in 2 ways:
    1) MMM should be MON
    2) YYY shoukd be YYYY

    So try:

    TO_DATE('" & Format(Now, "dd-mmm-yyyy") & "','dd-mon-yyyy')

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    THANK YOU!!!!!
    Nothing better than a good ride.

  8. #8
    Join Date
    Feb 2003
    Posts
    2

    Talking try this

    hi,
    Try using to_char(txtdate,'dd-mmm-yyyy') instead of to date

    I think this should work.
    bi.
    Gautham

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: try this

    Originally posted by egautham
    hi,
    Try using to_char(txtdate,'dd-mmm-yyyy') instead of to date

    I think this should work.
    bi.
    Gautham
    No, that is quite wrong. To convert a text string like txtdate to a date, you must use TO_DATE not TO_CHAR!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •