Query Help - Dates 1

[TAngel]

1) takes the Latest_Hire_Date and replace the year with the current year
2) If the resulting date is less than the current date then add a year to that date, otherwise leave it the way it is.

Example: 1

Latest Hire Date: 2/2/08
New Result: 2/2/10

Example 2

Latest Hire date: 9/8/73
New Result: 9/8/09

Thank you

 

[SantaMufasa]

TAngel,

Here is a solution:

SQL> select * from t_angel
  2  /

LAST_NAME                 LAST_HIRE
------------------------- ---------
Jones                     03-MAR-90
Ngao                      03-MAR-08
Ubu                       17-JUN-09
Quick-To-See              18-JUN-09
Ropeburn                  19-JUN-09

5 rows selected.

col x heading "Original|Last_Hire||Date"
col y heading "New|Last_Hire||Date"
select last_name
      ,last_hire_date x
      ,to_date(to_char(last_hire_date,'dd-mon-')||to_char(sysdate,'yyyy'),'dd-mon-yyyy') -- new date
           +decode(
              sign(
                 sysdate  -- take the current date, then subtract the "new date"
                 - to_date(to_char(last_hire_date,'dd-mon-')||to_char(sysdate,'yyyy'),'dd-mon-yyyy')
                  ),-1,0  -- if the result is negative (new date in future), then add (0 * 365) to the new date
                   ,1     -- otherwise (new date already past) add (1 * 365) to the new date
                  )*365 y
  from t_angel
/

                          Original  New
                          Last_Hire Last_Hire

LAST_NAME                 Date      Date
------------------------- --------- ---------
Jones                     03-MAR-90 03-MAR-10
Ngao                      03-MAR-08 03-MAR-10
Ubu                       17-JUN-09 17-JUN-10
Quick-To-See              18-JUN-09 18-JUN-10
Ropeburn                  19-JUN-09 19-JUN-09

5 rows selected.

Let us know if this is what you wanted and if you have any questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[TAngel]

This is perfect!! JUST what I needed. Thank you so very much.