DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Strage problem on date conversion

  1. #1
    Join Date
    Feb 2003
    Posts
    67

    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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>

  3. #3
    Join Date
    Feb 2003
    Posts
    67
    Stecal,

    What is the point you are making? You are getting values and I am getting zeros.

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    What do you get when you run following query

    select to_char(secdate, 'MM/DD/YYYY') from extable;

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    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.

  6. #6
    Join Date
    Feb 2003
    Posts
    67
    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

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Looks like YOU have a problem then. Good luck.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    is your to_date running ok for other tables? like ..


    select to_char(sysdate,'MM/DD/YYYY HH24:MM:SS') from dual;
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Feb 2003
    Posts
    67
    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?

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
  •  


Click Here to Expand Forum to Full Width