-
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.
-
MM is month!
Try HH24:MI:SS
-
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.
-
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
-
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.
-
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
/
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|