Sql代码
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression
The field you are extracting must be a field of the datetime_value_expr or interval_value_expr For example you can extract only YEAR MONTH and DAY from a DATE value Likewise you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE datatype
eg
?SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current Year
?SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current Month
?SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Day
?SELECT EXTRACT(HOUR FROM SYSDATE) FROM DUAL;Error
Correct:SELECT EXTRACT(HOUR FROM TIMESTAMP ::) FROM DUAL;
?SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;Error
Correct:SELECT EXTRACT(MINUTE FROM TIMESTAMP ::) FROM DUAL;
以上是EXTRACT()函数的使用说明英文部分有个很重要的部分就是extract 抽取年月日部分和时分部分的使用方法是不同的
EXTRACT(YEAR FROM SYSDATE)中FROM 的下一个参数是日期类型的时候可以抽取年月日就是说这个参数是日期类型的时候就可以抽取年\月\日而抽取不了时\分部分抽取时分部分得在FROM 后面加带TIMESTAMP 且后面加时间字符
这里要注意在(MINUTE FROM TIMESTAMP ::)中如果把 ::用日期类型的字段来代替并转换成字符类型后也是错误的估计是这方法的问题
比如(MINUTE FROM TIMESTAMP to_date(to_char(日期类型YYYYMMDD hh:mi:ss)YYYYMMDD hh:mi:ss))这样执行不了的
如果真要从日期类型的字段中抽取时\分部分用to_char() 函数就可以了比如to_char(日期类型hh:mi)
