Click here to Skip to main content
14,693,130 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i write this code but i have problem, it's show me this error (nvalid number):-

select count(distinct TASK_START_DATE) from prof_tasks 
inner join prof_employee_data on prof_employee_data.employee_id = prof_tasks.employee_id 
where prof_employee_data.emp_code= '12411' 
and extract(month from to_date(TASK_START_DATE,'dd/mm/yyyy')) = '01/10/2016'
and extract(year from to_date(TASK_START_DATE,'dd/mm/yyyy'))='01/10/2016' 
and allownce_id = 1


What I have tried:

i want select count For the month of October from the table.
Posted
Updated 28-Jul-20 2:43am
v3

You should try to replace:
select count(distinct TASK_START_DATE) from prof_tasks 
inner join prof_employee_data on prof_employee_data.employee_id = prof_tasks.employee_id 
where prof_employee_data.emp_code= '12411' 
and extract(month from to_date(TASK_START_DATE,'dd/mm/yyyy')) = '01/10/2016'
and extract(year from to_date(TASK_START_DATE,'dd/mm/yyyy'))='01/10/2016' 
and allownce_id = 1

by:
select count(distinct TASK_START_DATE) from prof_tasks 
inner join prof_employee_data on prof_employee_data.employee_id = prof_tasks.employee_id 
where prof_employee_data.emp_code= '12411' 
and extract(month from to_date(TASK_START_DATE,'dd/mm/yyyy')) = 10
and extract(year from to_date(TASK_START_DATE,'dd/mm/yyyy'))= 2016
and allownce_id = 1


Quote:
i want make the format date is complete like 01/10/2016 how i do that
I don't understand this!
Use Improve question to update your question.
Note that the comments are not working probably for the WE. I can see the comment but not answer.
   
v4
Comments
MahmoudOmar 25-Dec-16 3:20am
   
i want make the format date is complete like 01/10/2016 how i do that
Quote:
When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is an integer representing the datetime value in the Gregorian calendar.

So when you are extracting the year you can not check it against a date-like string, but against a integer representing the year:
and extract(year from to_date(TASK_START_DATE,'dd/mm/yyyy')) = 2016;

EXTRACT (datetime)[^]
   
v2
From Solution 5, what about just this:

to_char(task_start_date, 'yyyymm')
to_number(to_char(task_start_date, 'yyyymm'))
   
select extract(year from invoice_date)||extract(month from invoice_date) from fa_master2016
   
you can extract month and year as:
to_char(task_start_date, 'mm') and to_char(task_start_date, 'yyyy')
and if you want as numeric:
to_number(to_char(task_start_date, 'mm')) and to_number(to_char(task_start_date, 'yyyy'))
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900