Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    3

    Unanswered: Date format mask help

    Ok folks, this may be a bit of a n00b question, but I haven't played around with SQL queries in a few years so forgive me Anyway, I recently inherited a report that is generated from a spooled text file from SQL plus. I updated it a bit by inserting the date generated by a runtime variable into the first column of the text file to eliminate unnecessary manual labor. The problem that I'm having is that the date is in DD-MON-YY format but I need it to be MM/DD/YYYY. Any ideas?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Look into the TO_CHAR() function. It'll help you format the date,
    -cf

  3. #3
    Join Date
    Jul 2006
    Posts
    3
    Thanks! It looks like that worked, although I had to nest a TO_DATE inside of it and haven't tried it with the runtime variable in place of the string yet:

    TO_CHAR(TO_DATE('11-JUN-06','DD-MON-YY'), 'MM/DD/YYYY')

    Does the runtime variable have a default variable type (ie varchar2) when the accept command is used?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by DanDiggler
    Does the runtime variable have a default variable type (ie varchar2) when the accept command is used?
    It doesn't really have a type as such. The value you enter is simply substituted into the SQL before it is parsed:
    Code:
    SQL> select &col &keyword2 &table &keyword3 &col2 = &val;
    Enter value for col: *
    Enter value for keyword2: from
    Enter value for table: emp
    Enter value for keyword3: where
    Enter value for col2: ename
    Enter value for val: 'KING'
    old   1: select &col &keyword2 &table &keyword3 &col2 = &val
    new   1: select * from emp where ename = 'KING'
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
    (ACCEPT is just another way of setting these substitution variables).

  5. #5
    Join Date
    Jul 2006
    Posts
    3

    Thumbs up

    Got it, Thanks!

Posting Permissions

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