Results 1 to 10 of 10

Thread: datetime 9i

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: datetime 9i

    Hi guys

    I have a string '20101023101500' representing the date and time in a CSV file. The table I'm loading this into has a column of 'date' data type so I'm using the following to convert the string into date data type:

    Code:
    to_date(20101023101500,'YYYYMMDDHH24MISS')
    What I want to do is, somehow omit the 'seconds' '00' i.e., not load the seconds.

    I tried using to_char but then the date column will not accept the string.

    I can't (don't want to) manipulate the string in the CSV file either.

    The nls_date_format is DD/MM/RR.

    Hope I've given the whole picture. Could someone please help?

    Regards
    Shajju

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A DATE column contains seconds and there's nothing you can do about it.

    For reporting purposes, you can use TO_CHAR and format dates in any format you want (as long as it is supported). In other words, your SELECT would omit seconds, such as
    Code:
    select to_char(that_date_column, 'dd.mm.yyyy hh24:mi') from ...

  3. #3
    Join Date
    Aug 2008
    Posts
    464

    Thanks.

    OK thanks for the info.

    I take it changing the data type of the DATE column to VARCHAR(2) when it already contains date entries will not help either?

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    You see i tried creating a copy of a table by specifying:

    create table NEW_TABLE as
    (select * from OLD_TABLE)

    but got

    09:30:05 Error: ORA-00959: tablespace 'TBLESPACE1' does not exist

    I checked the list of tablespaces and it doesn't contain 'TBLESPACE1'. Not sure where this 'TBLESPACE1' is picked up from as it's not the tablespace of the table I'm trying to create a copy of.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Please ignore my last comment...Cannot change the data type of a column unless it's empty.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    I take it changing the data type of the DATE column to VARCHAR(2) when it already contains date entries will not help either?
    Help in what?
    Help in allowing storing "dates" not existing in reality like '20100431183000' (April, 31st)? Yes.
    Help in increasing space for storing it? Yes, as storing this string (without seconds) will need at least 12 characters (so VARCHAR2(2) will not be able to keep it), while DATE data type is stored in 7 bytes.

    You were talking about CSV file, but without describing the way how it is accessed in Oracle (external table?), it is really hard to propose anything. By the way, in the example in your first post, 20101023101500 is a numeric literal, not a string string one; but TO_DATE is probably (another assumptions) not used on literals, so it is confusing. Why do you not post verbatim (with eliminating some parts that are not important in your case) what you have? Or just create one table with DATE column only, CSV file containing only this date and construct a simple test case fully covering your issue?

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by shajju View Post
    changing the data type of the DATE column to VARCHAR(2)
    Don't be silly! Never store date values into columns whose datatype is different from DATE. Storing dates into CHAR columns is simply stupid, and all you can expect from doing that is bunch of problems.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    to_date(substr('20101023101500',1,12),'YYYYMMDDHH2 4MI')

    or
    trunc(to_date(20101023101500,'YYYYMMDDHH24MISS'),' mi')

    whatever floats your boat.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the suggestions but both

    select to_date(substr('20101023101500',1,12),'YYYYMMDDHH2 4MI') from dual

    select trunc(to_date(20101023101500,'YYYYMMDDHH24MISS'),' mi') from dual

    give me

    10/23/2010 10:15:00 AM



  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Thanks for the suggestions but both

    select to_date(substr('20101023101500',1,12),'YYYYMMDDHH2 4MI') from dual

    select trunc(to_date(20101023101500,'YYYYMMDDHH24MISS'),' mi') from dual

    give me

    10/23/2010 10:15:00 AM
    Where? What is the tool you are using (sqlplus, SQL Developer, Forms, JDBC, ...)?

    Please realize, that DATE data type has no format and contains date/time components with second granularity.
    Repeat this sentence until you accept it.

    If you want to display the column with DATE data type in specific format, use proper setting. E.g. NLS_DATE_FORMAT in sqlplus, the same (editable in Preferences window) in SQL Developer, cell property in Forms etc.
    Alternatively, you may use TO_CHAR to convert it explicitly to string, but then you may have problem if you will want to treat it further, as the result will have VARCHAR2 data type. Still curious how it is related to that CSV file remark in your first post.

Posting Permissions

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