Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Exclamation Unanswered: Initializing a variable in sql

    declare
    rundate number(5);
    Begin
    rundate := select to_char(to_date('01-'||to_char(sysdate,'MM-YYYY'),'DD-MM-YYYY')-1,'J')- to_char(to_date('01-01-1900', 'MM-DD-YYYY'),'J') as rundate from dual;
    end;

    i am trying to create a new variable and trying to initialise it with output of the select statement which returns a date in some XYZ format.

    Please Help .. I am new to this

    Cheers!
    subhotech
    Last edited by subhotech; 06-04-10 at 10:20.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DATE datatype is different from NUMBER datatype which is different from VARCHAR2 datatype.

    FORMAT only occurs at data presentation layer & does not exist within the database.

    do NOT depend upon implicit datatype conversion; use appropriate Oracle functions to assist changing 1 datatype to another (i.e. TO_DATE, TO_CHAR, TO_NUMBER)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In PL/SQL (as this is what your example is about, not "SQL"), you'd SELECT INTO. Something like
    Code:
    SQL> set serveroutput on
    SQL>
    SQL> declare
      2    ret_1 varchar2(10);
      3    ret_2 number;
      4  begin
      5    select to_char(sysdate, 'dd.mm.yyyy')
      6      into ret_1
      7      from dual;
      8
      9    select trunc(sysdate) - to_date('05.11.1955', 'dd.mm.yyyy')
     10      into ret_2
     11      from dual;
     12
     13    dbms_output.put_line('Today is ' || ret_1);
     14    dbms_output.put_line('It is ' || ret_2 || ' days since ' ||
     15                         'flux capacitor has been invented.');
     16  end;
     17  /
    Today is 04.06.2010
    It is 19935 days since flux capacitor has been invented.
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

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