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.
03-16-2005, 03:06 PM
MM is month!
03-16-2005, 03:15 PM
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.
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.
03-16-2005, 03:56 PM
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;
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.
03-16-2005, 04:53 PM
MESSAGE_DATe is actually stored in a date column, not a char or varchar2, right?
Run this ...
alter session set nls_date_format = 'DD-MM-YY HH24:MI:SS';
where rownum <10
and trunc(message_date) <> message_date
03-17-2005, 10:56 AM
It does not work. Just to recap and show the result of your query.
SQL> DESC MESSAGES
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:
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