Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Question Unanswered: Year function in Case statement does not work?

    A. - this item does not work with Year function in SELECT CASE statement

    SELECT f.EFF_DT, CASE f.END_DT WHEN YEAR(f.END_DT) = 9999 THEN 'Present' ELSE f.END_DT END
    From Customer F


    B. Same 'YEAR' function does work in WHERE clause.

    SELECT f.EFF_DT
    FROM Customer F
    Where YEAR(f.END_DT) = 9999


    Any thoughts, most appreciated...Thanks,
    Marcel K.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You have 2 problems:

    1. f.END_DT and "present" are not the same datatype.

    2. Wrong syntax for case. Try this (using current date instead of "present":

    SELECT f.END_DT,
    CASE YEAR(f.END_DT)
    WHEN 9999
    THEN current date [or anything in external date format]
    ELSE
    f.END_DT
    END
    From Customer F...

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    However...

    You can do this to get the datatypes the same:

    SELECT f.END_DT,
    CASE YEAR(f.END_DT)
    WHEN 9999
    THEN 'present'
    ELSE
    cast(f.END_DT as char(10))
    END
    From Customer F...

Posting Permissions

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