SQLでよく使う関数

■システム日付、システム時間

 ・Oracle のサーバ日付を得るには、次のようにする。
   (現在日付取得、現在時刻取得)

 select sysdate from dual;

 select to_char(
   sysdate,
   'yyyy.mm.dd hh24:mi:ss'
   )
   from dual
 ;

 select to_char(
   sysdate,
   'yyyy.mm.dd'
   )
   from dual
 ;

 select to_char(
   sysdate,
   'hh24:mi:ss'
   )
   from dual
 ;

 ＊ Access では、Now() を使用する。

■年の演算(年加算、年減算)

 ・date 型、または、to_date() で date 型にしたものに対して、年の演算を
   行うには、次のようにする。
   add_months では、12 ヶ月が 1 年となる。

 jcdt date;

 jcdt := add_months(jcdt, 12 * 1);   -- 1 年加算
 jcdt := add_months(jcdt, 12 * -1);  -- 1 年減算

 select add_months(sysdate, 12 * 1) from dual;

■月の演算(月加算、月減算)

 ・add_months を使用すると、12 月に 1 ヶ月加算すると翌年の 1 月になる。

 jcdt date;

 jcdt := add_months(jcdt, 1);   -- 1 ヶ月加算
 jcdt := add_months(jcdt, -1);  -- 1 ヶ月減算

 select add_months(sysdate, 1) from dual;

 ＊Access では、
      dateadd('m', 1, jcdt)
      dateadd('m', 1, now())
    を使用する。

■曜日の演算(曜日計算)

 ・前の週を計算するには、-7 すれば良い。

 jcdt date;

 jcdt := next_day(jcdt, '月');      -- 次の月曜計算
 jcdt := next_day(jcdt, '金');      -- 次の金曜計算
 jcdt := next_day(jcdt, '月') - 7;  -- 前の月曜計算

 select next_day(sysdate, '月') from dual;
 select next_day(sysdate, '月') - 7 from dual;

■週の演算(週加算、週減算)

 ・7 日を加減算すれば、週の計算となる。

 jcdt date;

 jcdt := jcdt + 1 * 7;  -- 1 週加算
 jcdt := jcdt - 1 * 7;  -- 1 週減算

 select sysdate + 1 * 7 from dual;

■月末日の演算

 ・月末日を求めるには、次のようにする。

 jcdt date;

 jcdt := last_day(jcdt);  -- 月末日

 select last_day(sysdate) from dual;

■日の演算(日加算、日減算)

 ・うるう年や大の月、小の月も考えなくて良い。

 jcdt date;

 jcdt := jcdt + 1;  -- 1 日加算(翌日)
 jcdt := jcdt - 1;  -- 1 日減算(前日)

 select sysdate + 1 from dual;

■日の 0 時

 ・date 型は、整数部で日を管理しているので、切り捨てれば 0 時となる。

 jcdt date;

 jcdt := trunc(jcdt);  -- jcdt 日の 0 時

 select trunc(sysdate) from dual;

 select to_char(
   trunc(sysdate),
   'yyyy.mm.dd hh24:mi:ss'
   )
   from dual
 ;

■時間の演算(時間加算、時間減算)

 ・date 型、小数部で時間を管理している。

 jcdt date;

 jcdt := jcdt + 1 / 24;  -- 1 時間加算
 jcdt := jcdt - 1 / 24;  -- 1 時間減算

 select sysdate + 1 / 24 from dual;

 select to_char(
   sysdate + 1 / 24,
   'yyyy.mm.dd hh24:mi:ss'
   )
   from dual
 ;

■分の演算(分加算、分減算)

 ・date 型、小数部で時間を管理しているので、24 * 60 = 1440 が分の単位と
   なる。

 jcdt date;

 jcdt := jcdt + 1 / 1440;  -- 1 分加算
 jcdt := jcdt - 1 / 1440;  -- 1 分減算

 select sysdate + 1 / 1440 from dual;

 select to_char(
   sysdate + 1 / 1440,
   'yyyy.mm.dd hh24:mi:ss'
   )
   from dual
 ;

■秒の演算(秒加算、秒減算)

 ・date 型、小数部で時間を管理しているので、1440 * 60 = 86400 が秒の
   単位となる。

 jcdt date;

 jcdt := jcdt + 1 / 86400;  -- 1 秒加算
 jcdt := jcdt - 1 / 86400;  -- 1 秒減算

 select sysdate + 1 / 86400 from dual;

 select to_char(
   sysdate + 1 / 86400,
   'yyyy.mm.dd hh24:mi:ss'
   )
   from dual
 ;

■日付間隔、時間間隔、期間計算、時間計算

 ・勤続年数や勤務時間など日付や時間の間隔を計算するには、次のようにする。
   月が整数部で、日以降が小数部で返る。(経過日数、期間内日数)

 jcdt date;
 mm number;

 mm := months_between(sysdate, jcdt);  -- SYSDATE と jcdt の期間

 select months_between(
   sysdate,
   to_date('2001.10.18 21:00:00', 'yyyy.mm.dd hh24:mi:ss')
   ) from dual;

 ・12 で割れば、年数が得られる。(経過年数計算、勤続年数計算、年齢計算)
   年に切り捨てるには trunc を使用すれば良い。

 select months_between(
   sysdate,
   to_date('1999.10.18 21:00:00', 'yyyy.mm.dd hh24:mi:ss')
   ) / 12
 from dual;

 select trunc(months_between(
   sysdate,
   to_date('1999.10.18 21:00:00', 'yyyy.mm.dd hh24:mi:ss')
   ) / 12)
 from dual;

■日付抽出、日付比較、日付判定、時間抽出、時間比較、時間判定

 ・日付の範囲や時間の範囲でレコードを抽出するには、次のようにする。
   日付に索引が付けてあるかにもよるが、一般的にも like より、日付として
   の抽出の方が高速と思う。
   (Date 型比較、日付型比較、日付大小比較、日付範囲指定)
   (日付データ抽出、日付検索、期間指定、期間範囲、期間集計、期間検索)

 select * from テーブル名
   where jcdt between
       to_date('2001.10.18 21:00:00', 'yyyy.mm.dd hh24:mi:ss')
     and
       to_date('2001.10.31 23:59:59', 'yyyy.mm.dd hh24:mi:ss')
 ;

 select * from テーブル名
   where jcdt > sysdate
 ;

 select * from テーブル名
   where jcdt >
       to_date('2001.10.18 21:00:00', 'yyyy.mm.dd hh24:mi:ss')
 ;

 select * from テーブル名
   where to_char(jcdt, 'yyyy.mm.dd hh24:mi:ss') like '2001%'
 ;