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

    Unanswered: oracle spool file

    Hi

    i am using spool file option in that date columns are there format is DD-MM-YY
    but i need to get the data yymmdd

    how to change below syntax to get date column as yymmdd instead ofDD-MM-YY

    spool c:\pclaimreg.txt
    set heading off
    set pages 0
    select CLAIM#||'|'||POL#||'|'||REN#||'|'||SEQ#||'|'||VEH# ||'|'||DRV#||'|'||DOL||'|'||DOR||'|'||DOP||'|'||CO MPANY||'|'||STATE2||'|'||PRODUCT||'|'||AGENT# FROM PCLAIMREG
    spool off;



    DOL,DOR,DOP are date fields,how to change the syntax to get date as yymmdd


    please advise me!!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Apply TO_CHAR function to DATE datatype columns; something like the following example:
    Code:
    SQL> select ename, job, to_char(hiredate, 'yymmdd') hd from emp where rownum <= 5;
    
    ENAME      JOB       HD
    ---------- --------- ------
    SMITH      CLERK     801217
    ALLEN      SALESMAN  810220
    WARD       SALESMAN  810222
    JONES      MANAGER   810402
    MARTIN     SALESMAN  810928
    Also, perhaps you could consider using the SET COLSEP SQL*Plus command so that you wouldn't have to concatenate column separator within the SELECT statement:
    Code:
    SQL> set colsep '|'
    SQL> select ename, job, to_char(hiredate, 'yymmdd') hd from emp where rownum <= 5;
    
    ENAME     |JOB      |HD
    ----------|---------|------
    SMITH     |CLERK    |801217
    ALLEN     |SALESMAN |810220
    WARD      |SALESMAN |810222
    JONES     |MANAGER  |810402
    MARTIN    |SALESMAN |810928

  3. #3
    Join Date
    Jan 2010
    Posts
    29
    Thanks it worked

Posting Permissions

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