Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: Date Help - shorten month?

    Here's the question I need help with:

    In name sequence, list mortal ID, first and last name and birth date for anyone whose plan expires after 1999. Display the date with this format: Jan. 22, 2001

    I've got all the code I nned except for one part, see below:

    Code:
    SQL> SELECT MORTAL.MORTAL_ID,
      2  TO_CHAR(DOB,'MONTH DD YYYY ') AS DATE_OF_BIRTH,
      3  FIRST_NAME||' '||LAST_NAME AS EXPIRES_AFTER_1999
      4  FROM MORTAL
      5  JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      6  JOIN POLICY ON INSURED.POLICY_ID=POLICY.POLICY_ID
      7  WHERE TO_CHAR(POLICY.EXPIRE_DATE, 'YYYY') <= 1999
      8  ORDER BY LAST_NAME;
    
     MORTAL_ID DATE_OF_BIRTH        EXPIRES_AFTER_1999
    ---------- -------------------- -------------------------
           106 FEBRUARY  22 1995    Charles JOHNSON
           105 JANUARY   31 1972    Tamara Johnson
           107 JUNE      22 1972    Randy Johnson
           120 MAY       20 1980    BEVERLY Schwitzer-Pless
           119 MAY       12 1962    Danielle Schwitzer-Pless
           111 JUNE      30 1953    Kimberly VAUBEL
           110 MARCH     02 1947    John Vaubel
    I need the date to be in this format: Jan. 22, 2001

    Any suggestions?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select to_char(sysdate,'Mon DD, YYYY') from dual;
    
    TO_CHAR(SYSDATE,'MOND
    ---------------------
    Nov 21, 2013
    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
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    SQL> select to_char(sysdate,'Mon DD, YYYY') from dual;
    
    TO_CHAR(SYSDATE,'MOND
    ---------------------
    Nov 21, 2013
    Thanks!
    Here's what I ended up with after collaboration w/ class mates, and google useage.

    Code:
    SQL> SELECT MORTAL.MORTAL_ID,
      2  TO_CHAR(DOB,'Mon. dd, YYYY') AS DATE_OF_BIRTH,
      3  FIRST_NAME||' '||LAST_NAME AS EXPIRES_AFTER_1999
      4  FROM MORTAL
      5  JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      6  JOIN POLICY ON INSURED.POLICY_ID=POLICY.POLICY_ID
      7  WHERE TO_CHAR(POLICY.EXPIRE_DATE, 'YYYY') <= 1999
      8  ORDER BY LAST_NAME;

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are close, but need to review the requirements closely. You missed something in the ORDER BY requirements.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE TO_CHAR(POLICY.EXPIRE_DATE, 'YYYY') <= 1999

    It poor coding to rely on implicit datatype conversion (between CHARACTER & NUMBER).
    It should be as below

    WHERE TO_CHAR(POLICY.EXPIRE_DATE, 'YYYY') <= '1999'
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's another nice function that can be used here:
    Code:
    SQL> select extract (year from sysdate) from dual;
    
    EXTRACT(YEARFROMSYSDATE)
    ------------------------
                        2013
    
    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
  •