Date & Time Question!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Date & Time Question!!!

Hybrid View

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Date & Time Question!!!

    Hi all,

    I have some weird problem, believe me! I have a table with a date column and bunch of other columns. After insert, i go and run a select statement and to_char the date like this, HH:MM:SS. It gives me the date right, but the time is always the same for each record that was inserted. The sysdate from dual gives the right time but I don't know why on insert the time is always the same. It seems like the clock has stopped.

    Can someone tell me how to go about and fix this or where to find the time to set it right? Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    MM is month!

    Try HH24:MI:SS

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    TO_CHAR(MESSAGE_DATE, 'MM-DD-RR HH:MM:SS')
    Before it was this: 03-16-05 12:03:00


    TO_CHAR(MESSAGE_DATE, 'MM-DD-RR HH24:MI:SS')
    With yours it is this: 03-16-05 00:00:00

    I am not sure what to do.

    Code:
    But this is not issue. I am wanting to know why the date is always the same for my inserts. That is the question. Please read carefully. Thanks for your replies.
    Last edited by see_one; 03-16-2005 at 05:36 PM.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    SQL> select TO_CHAR(sysdate, 'MM-DD-RR H24:MI:SS') from dual;
    select TO_CHAR(sysdate, 'MM-DD-RR H24:MI:SS') from dual
                            *
    ERROR at line 1:
    ORA-01821: date format not recognized
    
    
    SQL> select TO_CHAR(sysdate, 'MM-DD-RR HH24:MI:SS') from dual;
    
    TO_CHAR(SYSDATE,'
    -----------------
    03-16-05 13:56:25
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2004
    Posts
    125
    I want to know, why my records always get the same time, even though they have been inserted at different times of the day? If I use to_char to get date with time, like in an above examples, the time part of Message_date column gives me the same time. Why? Thanks.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    MESSAGE_DATe is actually stored in a date column, not a char or varchar2, right?

    Run this ...
    Code:
    alter session set nls_date_format = 'DD-MM-YY HH24:MI:SS';
    
    Select message_date,
           to_char(message_date,'DD-MM-YY HH24:MI:SS')
    from   my_table
    where  rownum <10
    and    trunc(message_date) <> message_date
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2004
    Posts
    125
    It does not work. Just to recap and show the result of your query.

    SQL> DESC MESSAGES

    Code:
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    REG_NUM                                   NOT NULL VARCHAR2(14)
    MESS_TYPE                                 NOT NULL CHAR(1)
    MESSAGE_DATE                              NOT NULL DATE
    MESSAGE                                   NOT NULL VARCHAR2(255)
    INITIALS                                  NOT NULL VARCHAR2(3)
    With your query:


    Code:
    SQL> Select message_date,
      2         to_char(message_date,'DD-MM-YY HH24:MI:SS')
      3  from   MESSAGES
      4  where  REG_NUM = '1266-23-EXMV'
      5  and    trunc(message_date) <> message_date
      6  /
    
    no rows selected
    With truc is commented out:
    Code:
      1  Select message_date,
      2         to_char(message_date,'DD-MM-YY HH24:MI:SS')
      3  from   MESSAGES
      4  where  REG_NUM = '1266-23-EXMV'
      5* --and    trunc(message_date) <> message_date
    SQL> /
    
    MESSAGE_DATE      TO_CHAR(MESSAGE_D
    ----------------- -----------------
    12-12-03 00:00:00 12-12-03 00:00:00
    15-12-03 00:00:00 15-12-03 00:00:00
    16-12-03 00:00:00 16-12-03 00:00:00
    22-12-03 00:00:00 22-12-03 00:00:00
    29-12-03 00:00:00 29-12-03 00:00:00
    31-12-03 00:00:00 31-12-03 00:00:00
    02-01-04 00:00:00 02-01-04 00:00:00
    05-01-04 00:00:00 05-01-04 00:00:00
    15-12-03 00:00:00 15-12-03 00:00:00
    15-12-03 00:00:00 15-12-03 00:00:00
    15-12-03 00:00:00 15-12-03 00:00:00
    
    MESSAGE_DATE      TO_CHAR(MESSAGE_D
    ----------------- -----------------
    15-12-03 00:00:00 15-12-03 00:00:00
    16-12-03 00:00:00 16-12-03 00:00:00
    22-12-03 00:00:00 22-12-03 00:00:00
    29-12-03 00:00:00 29-12-03 00:00:00
    31-12-03 00:00:00 31-12-03 00:00:00
    02-01-04 00:00:00 02-01-04 00:00:00
    09-01-04 00:00:00 09-01-04 00:00:00
    26-01-04 00:00:00 26-01-04 00:00:00
    04-02-04 00:00:00 04-02-04 00:00:00
    04-02-04 00:00:00 04-02-04 00:00:00
    01-02-05 00:00:00 01-02-05 00:00:00
    
    MESSAGE_DATE      TO_CHAR(MESSAGE_D
    ----------------- -----------------
    16-03-05 00:00:00 16-03-05 00:00:00
    16-03-05 00:00:00 16-03-05 00:00:00
    
    24 rows selected.
    It gives me no time. Thanks to all for your help. I'll find a way.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    So there time part is always 00:00:00 - the problem must be with the insert statement. Would you please post it.

Posting Permissions

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


Click Here to Expand Forum to Full Width