我如何从oracle中提取月份和年份 [英] How I extract month and year from date in oracle

问题描述

我写这段代码但是我有问题，它告诉我这个错误（无效的号码）： -

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

我的尝试：



i想要从表中选择10月份。

What I have tried:

i want select count For the month of October from the table.

推荐答案

你应该尝试更换：

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：

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

引用：

我想让格式日期像2016年10月1日一样完整我是怎么做的

i want make the format date is complete like 01/10/2016 how i do that

我d不明白这个！

使用改善问题更新你的问题。

请注意，评论不是可能为WE工作。我可以看到评论但没有回答。

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.

Quote：

当你提取TIMEZONE_REGION或TIMEZONE_ABBR时（缩写），返回的值是一个包含适当时区名称或缩写的字符串。 当您提取任何其他值时，返回的值是一个整数，表示公历中的日期时间值。

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） [ ^ ]

select extract（来自invoice_date的年份）来自fa_master2016的|| extract（来自invoice_date的月份）

select extract(year from invoice_date)||extract(month from invoice_date) from fa_master2016

这篇关于我如何从oracle中提取月份和年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！