有一张表,有年字段year,时期字段type,期数term,如下
ID year type term
1 2010 R 1
2 2010 R 2
. . . .
. . . .
N 2010 R 365其中R为日类型,term中1代表1月1日,365或者366代表12月31日,
现在需要把term转换为X月X日,同时还需要判断闰年,有什么好的办法吗?尽量使用查询。
ID year type term
1 2010 R 1
2 2010 R 2
. . . .
. . . .
N 2010 R 365其中R为日类型,term中1代表1月1日,365或者366代表12月31日,
现在需要把term转换为X月X日,同时还需要判断闰年,有什么好的办法吗?尽量使用查询。
解决方案 »
- oracle10采用jdbc的方式获取conn,并设置conn.setAutoCommit(false),不显示执行conn.commit();结果还是提交了。
- ORA-04098 触发器无效且未通过重新验证问题
- oracle 10g 输出文件路径问题?
- 这个SQL我应该怎么写
- oracle嵌套表赋值问题
- 从Oracle网站上下载的ORACLE8.1.7 FOR AIX能否在AIX4.3.3上安装???
- oracle客户端安装
- oracle编程入门书籍,急!
- 如何安装oracle的odbc驱动
- 求教一下大神们,PL/SQL怎么对EXCEL里的数据做批量处理
- oracle日期函数
- 两段封锁协议用于解决什么问题
select '2000' year, 'R' type, 1 term from dual union all
select '2000', 'R', 58 from dual union all
select '2000', 'R', 59 from dual union all
select '2000', 'R', 60 from dual union all
select '2000', 'R', 128 from dual union all
select '2000', 'R', 129 from dual union all
select '2000', 'R', 130 from dual union all
select '2000', 'R', 365 from dual union all
select '2000', 'R', 366 from dual
)
select year,
type,
term,
to_char(to_date(year||term,'yyyyddd'),'mmdd') mon_day
from tab--result:
YEAR TYPE TERM MON_DAY
--------------------------
2000 R 1 0101
2000 R 58 0227
2000 R 59 0228
2000 R 60 0229
2000 R 128 0507
2000 R 129 0508
2000 R 130 0509
2000 R 365 1230
2000 R 366 1231
to_char(to_date(year||term,'yyyyddd'),'mmdd') month_day
from 表
In Oracle/PLSQL, the TO_DATE function converts a string to a date.
The syntax for the TO_DATE function is:
to_date( string1, [ format_mask ], [ nls_language ] )string1 is the string that will be converted to a date.
format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.
The following is a list of options for the format_mask parameter.
These parameters can be used in many combinations.Parameter Explanation
----------------------------------------
YEAR Year, spelled out
----------------------------------------
YYYY 4-digit year
YYY
YY
----------------------------------------
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
----------------------------------------
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
RRRR Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
----------------------------------------
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
----------------------------------------
MM Month (01-12; JAN = 01).
----------------------------------------
MON Abbreviated name of month.
----------------------------------------
MONTH Name of month, padded with blanks to length of 9 characters.
----------------------------------------
RM Roman numeral month (I-XII; JAN = I).
----------------------------------------
WW Week of year (1-53) where week 1 starts on the first day of the year and
continues to the seventh day of the year.--注意这里
----------------------------------------
W Week of month (1-5) where week 1 starts on the first day of the month
and ends on the seventh.
----------------------------------------
IW Week of year (1-52 or 1-53) based on the ISO standard.--注意这里
----------------------------------------
D Day of week (1-7).
----------------------------------------
DAY Name of day.
----------------------------------------
DD Day of month (1-31).
----------------------------------------
DDD Day of year (1-366).---注意这里
----------------------------------------
DY Abbreviated name of day.
----------------------------------------
J Julian day; the number of days since January 1, 4712 BC.
----------------------------------------
HH Hour of day (1-12).
----------------------------------------
HH12 Hour of day (1-12).
----------------------------------------
HH24 Hour of day (0-23).
----------------------------------------
MI Minute (0-59).
----------------------------------------
SS Second (0-59).
----------------------------------------
SSSSS Seconds past midnight (0-86399).
----------------------------------------
FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits
in the fractional seconds. For example, 'FF4'.
----------------------------------------
AM, A.M., PM, Meridian indicator
or P.M.
----------------------------------------
AD or A.D AD indicator
----------------------------------------
BC or B.C. BC indicator
----------------------------------------
TZD Daylight savings information. For example, 'PST'
----------------------------------------
TZH Time zone hour.
----------------------------------------
TZM Time zone minute.
----------------------------------------
TZR Time zone region.
PL/SQL中如何判断闰年
--其实判断是否是闰年最简单的就是直接看2月份的天数就可以了
--即:to_char( last_day( to_date(&year||'02','yyyymm') ), 'dd' )
--如果是28就不是闰年
SQL*Plus: Release 8.0.6.0.0 - Production on 星期四 5月 12 09:37:37 2011(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> select to_char( last_day( to_date(&year||'02','yyyymm') ), 'dd' ) from dual;
Enter value for year: 2000
old 1: select to_char( last_day( to_date(&year||'02','yyyymm') ), 'dd' ) from dual
new 1: select to_char( last_day( to_date(2000||'02','yyyymm') ), 'dd' ) from dualTO_C
----
29SQL> /
Enter value for year: 2010
old 1: select to_char( last_day( to_date(&year||'02','yyyymm') ), 'dd' ) from dual
new 1: select to_char( last_day( to_date(2010||'02','yyyymm') ), 'dd' ) from dualTO_C
----
28SQL>
2 when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
3 then 'the year you input is a leap year'
4 else 'the year you input is not a leap year'
5 end is_leap_year
6 from dual;
Enter value for year: 2050
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(2050||'02','yyyymm')),'dd')='29'IS_LEAP_YEAR
-------------------------------------
the year you input is not a leap yearSQL> /
Enter value for year: 2000
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(2000||'02','yyyymm')),'dd')='29'IS_LEAP_YEAR
---------------------------------
the year you input is a leap yearSQL> /
Enter value for year: 2012
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(2012||'02','yyyymm')),'dd')='29'IS_LEAP_YEAR
---------------------------------
the year you input is a leap yearSQL> /
Enter value for year: 1998
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(1998||'02','yyyymm')),'dd')='29'IS_LEAP_YEAR
-------------------------------------
the year you input is not a leap year参考:
oracle 闰年判断