Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: oracle and loop question

    Hi All,

    I have a question; in oracle.

    say TIME=20060928
    desired output: 20060927

    say TIME=20061001
    DESIRED output: 20060930

    If i substract one day from 'TIME' i am looking for previous day.

    can anyone please suggest me how do i do this is SQL.

    thanks
    mark

  2. #2
    Join Date
    Oct 2005
    Posts
    92

    Date conversion

    Hi All,

    thanks to all of you for your suggestions;

    this is how i tried,

    SQL> select to_date('20060928', 'YYYYMMDD') - 1 from dual;


    TO_DATE('
    ---------
    27-SEP-06


    my desired output in this case is 20060927 but here the output is
    coming as 27-SEP-06.


    can anyone please suggest me how do I get the output as 20060927


    thanks a bunch
    mark

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The result you got is displayed according to your NLS settings which tell Oracle how to display date values. In order to change it according to your needs, simply convert this DATE value into a CHARACTER one using adequate format mask. In your example, this would be
    Code:
    SQL> select to_char(to_date('20060928', 'yyyymmdd') - 1, 'yyyymmdd') from dual;
    
    TO_CHAR(
    --------
    20060927
    
    SQL>
    Or, another option would be
    Code:
    SQL> select sysdate from dual;
    
    SYSDATE
    --------
    29.09.06
    
    SQL> alter session set nls_date_format = 'yyyymmdd';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    --------
    20060929
    
    SQL>
    [EDIT] Just being curious - what does the thread name ("Oracle and loop question") have to do with the actual problem?

Posting Permissions

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