-
Strage problem on date conversion
I have a data column in a table. If I select as date I get a value. If I select to to char I get all zeros.
SQL> desc extable
Name Null? Type
----------------------------------------- -------- ----------------
BRIEFID NOT NULL NUMBER(10)
SECPERSONID NOT NULL NUMBER(10)
SECDATE NOT NULL DATE
SECTYPE NOT NULL VARCHAR2(20)
MODIFYDATE NOT NULL DATE
SQL> select secdate from extable
2 where rownum < 6;
02/26/2001
02/26/2001
02/26/2001
02/26/2001
02/26/2001
1 select to_char(secdate, 'MM/DD/YYYY HH24:MM:SS') from extable where rownum < 6
SQL> /
00/00/0000 00:00:00
00/00/0000 00:00:00
00/00/0000 00:00:00
00/00/0000 00:00:00
00/00/0000 00:00:00
SQL> show parameter NLS_DATE_FORMAT
nls_date_format string mm/dd/yyyy
-
Code:
SQL> create table dba_date (in_date date);
Table created.
SQL> insert into dba_date values (sysdate);
1 row created.
(repeated five times)
1* select to_char(in_date, 'MM/DD/YYYY HH24:MM:SS')
from dba_date
SQL> /
TO_CHAR(IN_DATE,'MM
-------------------
04/07/2003 14:04:25
04/07/2003 14:04:26
04/07/2003 14:04:26
04/07/2003 14:04:27
04/07/2003 14:04:28
04/07/2003 14:04:28
6 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select to_char(in_date, 'MM/DD/YYYY HH24:MM:SS')
2 from dba_date
3* where rownum < 4
SQL> /
TO_CHAR(IN_DATE,'MM
-------------------
04/07/2003 14:04:25
04/07/2003 14:04:26
04/07/2003 14:04:26
SQL>
-
Stecal,
What is the point you are making? You are getting values and I am getting zeros.
-
What do you get when you run following query
select to_char(secdate, 'MM/DD/YYYY') from extable;
-
Originally posted by sureshot
Stecal,
What is the point you are making? You are getting values and I am getting zeros.
My point? My point is that I have nothing better to do at work than to spend time demonstrating to you why something you wrote/coded works/doesn't work on this forum. You can at least see that what you wrote is fine with respect to syntax when someone else uses the code to demonstrate a proof of concept to you.
-
stecal, I posted the syntax and you could have said whether it is correct or incorrect.
And I did not write the code - This is a 1997 - 803 database which we upgraded to 9.2.
irehman,
1 select to_char(secdate,'MM/DD/YYYY') from
2* extable where rownum < 6
SQL> /
00/00/0000
00/00/0000
00/00/0000
00/00/0000
00/00/0000
-
Looks like YOU have a problem then. Good luck.
-
is your to_date running ok for other tables? like ..
select to_char(sysdate,'MM/DD/YYYY HH24:MM:SS') from dual;
-
Any one has any ideas on how to troubleshoot this?
Looked like a simple problem to me and I tried different things - I can select the data as date - only if I tried to convert the datatype I get zeros.
Jurij... where are you man?
-
What we have here, IMHO, is a data mis-understanding. First, rownum is irrelevent without an order. I suggest you try your query with something like:
select * from
(select to_char(secdate,'MM/DD/YYYY') char_secdate from extable
order by secdate)
where rownum < 6
Second, I would check to see what the real data is in contrast to the formatted data. For example:
select * from
(select to_char(secdate,'MM/DD/YYYY') char_secdate,
sec_date
from extable
order by secdate)
where rownum < 6
Jeff Hunter
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
|