Click here to Skip to main content
15,867,330 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):-

SQL
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 1:43am
v3

You should try to replace:
SQL
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:
SQL
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.
 
Share this 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:
SQL
and extract(year from to_date(TASK_START_DATE,'dd/mm/yyyy')) = 2016;

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

to_char(task_start_date, 'yyyymm')
to_number(to_char(task_start_date, 'yyyymm'))
 
Share this answer
 
select extract(year from invoice_date)||extract(month from invoice_date) from fa_master2016
 
Share this answer
 
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'))
 
Share this answer
 

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