2009年06月19日


日付の丸めと切捨て

週の丸めと切捨て
select
	'2009/11/1' as "11月1日は月曜",
	round(TO_DATE('2009/11/1'),'DAY') as ROUND,
	trunc(TO_DATE('2009/11/1'),'DAY') as TRUNC
 from dual
union all
select
	'2009/11/2',
	round(TO_DATE('2009/11/2'),'DAY'),
	trunc(TO_DATE('2009/11/2'),'DAY') as TRUNC
 from dual
union all
select
	'2009/11/3',
	round(TO_DATE('2009/11/3'),'DAY'),
	trunc(TO_DATE('2009/11/3'),'DAY') as TRUNC
 from dual
union all
select 
	'2009/11/4',
	round(TO_DATE('2009/11/4'),'DAY'),
	trunc(TO_DATE('2009/11/4'),'DAY') as TRUNC
 from dual
union all
select 
	'2009/11/5',
	round(TO_DATE('2009/11/5'),'DAY'),
	trunc(TO_DATE('2009/11/5'),'DAY') as TRUNC
 from dual
union all
select
	'2009/11/6',
	round(TO_DATE('2009/11/6'),'DAY'),
	trunc(TO_DATE('2009/11/6'),'DAY') as TRUNC
 from dual
union all
select 
	'2009/11/7',
	round(TO_DATE('2009/11/7'),'DAY'),
	trunc(TO_DATE('2009/11/7'),'DAY') as TRUNC
 from dual
union all
select 
	'2009/11/8',
	round(TO_DATE('2009/11/8'),'DAY'),
	trunc(TO_DATE('2009/11/8'),'DAY') as TRUNC
 from dual
11月1日は月曜ROUNDTRUNC
2009/11/12009/11/012009/11/01
2009/11/22009/11/012009/11/01
2009/11/32009/11/012009/11/01
2009/11/42009/11/012009/11/01
2009/11/52009/11/082009/11/01
2009/11/62009/11/082009/11/01
2009/11/72009/11/082009/11/01
2009/11/82009/11/082009/11/08

月の丸めと切捨て
16 日になると切り上がります
select
	'2009/11/1' as "BASE",
	round(TO_DATE('2009/11/1'),'MONTH') as ROUND,
	trunc(TO_DATE('2009/11/1'),'MONTH') as TRUNC
 from dual
union all
select
	'2009/11/15',
	round(TO_DATE('2009/11/15'),'MONTH'),
	trunc(TO_DATE('2009/11/15'),'MONTH') as TRUNC
 from dual
union all
select
	'2009/11/16',
	round(TO_DATE('2009/11/16'),'MONTH'),
	trunc(TO_DATE('2009/11/16'),'MONTH') as TRUNC
 from dual
union all
select 
	'2009/2/15',
	round(TO_DATE('2009/2/15'),'MONTH'),
	trunc(TO_DATE('2009/2/15'),'MONTH') as TRUNC
 from dual
union all
select 
	'2009/2/16',
	round(TO_DATE('2009/2/16'),'MONTH'),
	trunc(TO_DATE('2009/2/16'),'MONTH') as TRUNC
 from dual

BASEROUNDTRUNC
2009/11/12009/11/012009/11/01
2009/11/152009/11/012009/11/01
2009/11/162009/12/012009/11/01
2009/2/152009/02/012009/02/01
2009/2/162009/03/012009/02/01

年の丸めと切捨て
1) 日付は月初になる
2) 7月になると切り上がります
select
	'2009/1/10' as "BASE",
	round(TO_DATE('2009/1/10'),'YEAR') as ROUND,
	trunc(TO_DATE('2009/1/10'),'YEAR') as TRUNC
 from dual
union all
select
	'2009/6/10',
	round(TO_DATE('2009/6/10'),'YEAR') as ROUND,
	trunc(TO_DATE('2009/6/10'),'YEAR') as TRUNC
 from dual
union all
select
	'2009/7/10',
	round(TO_DATE('2009/7/10'),'YEAR') as ROUND,
	trunc(TO_DATE('2009/7/10'),'YEAR') as TRUNC
 from dual

BASEROUNDTRUNC
2009/1/102009/01/012009/01/01
2009/6/102009/01/012009/01/01
2009/7/102010/01/012009/01/01


posted by at 2009-06-19 12:16 | 暗記 | このブログの読者になる | 更新情報をチェックする
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。