Home » SQL & PL/SQL » SQL & PL/SQL » Trying to get employee hire annyversary (Oracle 11g)
Trying to get employee hire annyversary [message #569034] Thu, 18 October 2012 17:58 Go to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
Hi All,

I'm trying to get employee hire anniversary between 2 days ago and within 7 days from today
It's working fine until I get to January system month (then mm=01, which is less then mm=12)
Can anyone help solving this, please?

select employee_id, first_name, last_name, job_id, hire_date,
case 
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-2), 'mmdd'), '9999')) then 'Day before yesterday'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-1), 'mmdd'), '9999')) then 'Yesterday'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate), 'mmdd'), '9999')) then 'Today'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate+1), 'mmdd'), '9999')) then 'Tomorrow'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate+2), 'mmdd'), '9999')) then 'Day after tomorrow'
  else 'Later this week'
end as ANNYVERSARY
from employees
where to_number(to_char(hire_date, 'mmdd'), '9999') between to_number(to_char((sysdate-2), 'mmdd'), '9999') and to_number(to_char((sysdate+7), 'mmdd'), '9999')
order by hire_date

[Updated on: Thu, 18 October 2012 17:59]

Report message to a moderator

Re: Trying to get employee hire annyversary [message #569035 is a reply to message #569034] Thu, 18 October 2012 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 22897
Registered: January 2009
Senior Member
instead of 'mmdd' utilize 'yyyymmdd'
Re: Trying to get employee hire annyversary [message #569036 is a reply to message #569035] Thu, 18 October 2012 18:06 Go to previous messageGo to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
Using
select employee_id, first_name, last_name, job_id, hire_date,
case 
  when (to_number(to_char(hire_date,'yyyymmdd'),'9999')) = (to_number(to_char((sysdate-2), 'yyyymmdd'), '9999')) then 'Day before yesterday'
  when (to_number(to_char(hire_date,'yyyymmdd'),'9999')) = (to_number(to_char((sysdate-1), 'yyyymmdd'), '9999')) then 'Yesterday'
  when (to_number(to_char(hire_date,'yyyymmdd'),'9999')) = (to_number(to_char((sysdate), 'yyyymmdd'), '9999')) then 'Today'
  when (to_number(to_char(hire_date,'yyyymmdd'),'9999')) = (to_number(to_char((sysdate+1), 'yyyymmdd'), '9999')) then 'Tomorrow'
  when (to_number(to_char(hire_date,'yyyymmdd'),'9999')) = (to_number(to_char((sysdate+2), 'yyyymmdd'), '9999')) then 'Day after tomorrow'
  else 'Later this week'
end as ANNYVERSARY
from employees
where to_number(to_char(hire_date, 'yyyymmdd'), '9999') between to_number(to_char((sysdate-2), 'yyyymmdd'), '9999') and to_number(to_char((sysdate+7), 'yyyymmdd'), '9999')
order by hire_date

I get the following
ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

[Updated on: Thu, 18 October 2012 18:06]

Report message to a moderator

Re: Trying to get employee hire annyversary [message #569037 is a reply to message #569036] Thu, 18 October 2012 18:16 Go to previous messageGo to next message
BlackSwan
Messages: 22897
Registered: January 2009
Senior Member
date was increased in size from 4 to 8 digits, so you need to change mask from '9999' to '99999999'
Re: Trying to get employee hire annyversary [message #569038 is a reply to message #569037] Thu, 18 October 2012 18:29 Go to previous messageGo to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
Thank you for prompt and quick reply.

However, running this
select employee_id, first_name, last_name, job_id, hire_date,
case 
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-2), 'mmdd'), '9999')) then 'Day before yesterday'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-1), 'mmdd'), '9999')) then 'Yesterday'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate), 'mmdd'), '9999')) then 'Today'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate+1), 'mmdd'), '9999')) then 'Tomorrow'
  when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate+2), 'mmdd'), '9999')) then 'Day after tomorrow'
  else 'Later this week'
end as ANNYVERSARY
from employees
where to_number(to_char(hire_date, 'mmdd'), '9999') between to_number(to_char((sysdate-2), 'mmdd'), '9999') and to_number(to_char((sysdate+7), 'mmdd'), '9999')
order by hire_date

I get a result, but running this
select employee_id, first_name, last_name, job_id, hire_date,
case 
  when (to_number(to_char(hire_date,'yyyymmdd'),'99999999')) = (to_number(to_char((sysdate-2), 'yyyymmdd'), '99999999')) then 'Day before yesterday'
  when (to_number(to_char(hire_date,'yyyymmdd'),'99999999')) = (to_number(to_char((sysdate-1), 'yyyymmdd'), '99999999')) then 'Yesterday'
  when (to_number(to_char(hire_date,'yyyymmdd'),'99999999')) = (to_number(to_char((sysdate), 'yyyymmdd'), '99999999')) then 'Today'
  when (to_number(to_char(hire_date,'yyyymmdd'),'99999999')) = (to_number(to_char((sysdate+1), 'yyyymmdd'), '99999999')) then 'Tomorrow'
  when (to_number(to_char(hire_date,'yyyymmdd'),'99999999')) = (to_number(to_char((sysdate+2), 'yyyymmdd'), '99999999')) then 'Day after tomorrow'
  else 'Later this week'
end as ANNYVERSARY
from employees
where to_number(to_char(hire_date, 'yyyymmdd'), '99999999') between to_number(to_char((sysdate-2), 'yyyymmdd'), '99999999') and to_number(to_char((sysdate+7), 'yyyymmdd'), '99999999')
order by hire_date

I'm getting empty results

BTW, do I have to use the mask?

[Updated on: Thu, 18 October 2012 18:30]

Report message to a moderator

Re: Trying to get employee hire annyversary [message #569039 is a reply to message #569038] Thu, 18 October 2012 18:51 Go to previous messageGo to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
I know why YYYYMMDD doesn't return anything
The only matching result is 17-Oct-2003
However, I'm comparing 2012 to 2003 and therefor it's getting empty results
Re: Trying to get employee hire annyversary [message #569085 is a reply to message #569034] Fri, 19 October 2012 11:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Your solution, in general, will not work for people hired February 29. Check this. Based on it:

select  employee_id,
        first_name,
        last_name,
        job_id,
        hire_date,
        case add_months(hire_date,offset) - trunc(sysdate)
          when -2 then 'Day before yesterday'
          when -1 then 'Yesterday'
          when 0 then 'Today'
          when 1 then 'Tomorrow'
          when 2 then 'Day after tomorrow'
          else 'Within 3 to 7 days'
        end as ANNYVERSARY
  from  hr.employees,
        (
         select  (level - 1) * 12 offset
           from  dual
           connect by level <= 151
        )
  where add_months(hire_date,offset) between trunc(sysdate) - 2 and trunc(sysdate) + 7
  order by hire_date
/

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID     HIRE_DATE ANNYVERSARY
----------- ---------- --------- ---------- --------- --------------------
        141 Trenna     Rajs      ST_CLERK   17-OCT-03 Day before yesterday
        138 Stephen    Stiles    ST_CLERK   26-OCT-05 Within 3 to 7 days

SQL> 


SY.
Re: Trying to get employee hire annyversary [message #569086 is a reply to message #569085] Fri, 19 October 2012 12:25 Go to previous messageGo to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
I have to use to_char and to_number
Re: Trying to get employee hire annyversary [message #569087 is a reply to message #569086] Fri, 19 October 2012 12:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
IgalSc wrote on Fri, 19 October 2012 13:25
I have to use to_char and to_number


And I thought you have to get employee hire anniversary Shocked

SY.
Re: Trying to get employee hire annyversary [message #569088 is a reply to message #569087] Fri, 19 October 2012 12:30 Go to previous messageGo to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
I do, but using to_char and to_number
Re: Trying to get employee hire annyversary [message #569089 is a reply to message #569088] Fri, 19 October 2012 12:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
This makes no sense. Are you saying your customer insists on what built-in functions you must use???

SY.
Re: Trying to get employee hire annyversary [message #569090 is a reply to message #569089] Fri, 19 October 2012 12:47 Go to previous messageGo to next message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
no, it's assignment at the oracle course
Re: Trying to get employee hire annyversary [message #569091 is a reply to message #569090] Fri, 19 October 2012 14:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
IgalSc wrote on Fri, 19 October 2012 13:47
no, it's assignment at the oracle course


select  employee_id,
        first_name,
        last_name,
        job_id,
        hire_date,
        case to_number(to_char(add_months(hire_date,offset),'yyyymmdd'))
          when to_number(to_char(sysdate - 2,'yyyymmdd')) then 'Day before yesterday'
          when to_number(to_char(sysdate - 1,'yyyymmdd')) then 'Yesterday'
          when to_number(to_char(sysdate,'yyyymmdd')) then 'Today'
          when to_number(to_char(sysdate + 1,'yyyymmdd')) then 'Tomorrow'
          when to_number(to_char(sysdate + 2,'yyyymmdd')) then 'Day after tomorrow'
          else 'Within 3 to 7 days'
        end as anniversary
  from  hr.employees,
        (
         select  (level - 1) * 12 offset
           from  dual
           connect by level <= 151
        )
  where  to_number(to_char(add_months(hire_date,offset),'yyyymmdd')) between to_number(to_char(sysdate - 2,'yyyymmdd'))
                                                                         and to_number(to_char(sysdate + 7,'yyyymmdd'))
  order by hire_date
/

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID     HIRE_DATE ANNIVERSARY
----------- ---------- --------- ---------- --------- --------------------
        141 Trenna     Rajs      ST_CLERK   17-OCT-03 Day before yesterday
        138 Stephen    Stiles    ST_CLERK   26-OCT-05 Within 3 to 7 days

SQL> 


SY.
Re: Trying to get employee hire annyversary [message #569110 is a reply to message #569091] Sat, 20 October 2012 10:06 Go to previous message
IgalSc
Messages: 8
Registered: October 2012
Location: Toronto, Canada
Junior Member
thank you, sir!
Previous Topic: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link
Next Topic: Help for SQL
Goto Forum:
  


Current Time: Mon Oct 20 05:11:08 CDT 2014

Total time taken to generate the page: 0.05775 seconds