SQL*PLUSで日付型をWHERE句で比較するときの注意点
Oracle SQL*PLUSで日付型をWHERE句で比較するときの注意点について備忘録として記載する。
例題
Oracleデータベース テーブル構成
テーブル名:NAME_TABLE
名称 :NAME :VARCHAR2
使用開始日:STARTDATE:DATE
使用終了日:ENDDATE :DATE
※STARTDATE、ENDDATEは時分秒は入れない。(00:00:00になっている)
※日付型のフォーマット(nls_date_format)は'YYYY/MM/DD HH24:MI:SS'形式となっている。
上記テーブル構成で現在日付(SYSDATE)において有効な名称(使用開始日 ≦ 現在日付 ≦ 使用終了日の名称)を取得する場合のSQL
SELECT NAME FROM NAME_TABLE
WHERE
STARTDATE <= TRUNC(SYSDATE)
AND
ENDDATE >= TRUNC(SYSDATE);
または
SELECT NAME FROM NAME_TABLE
WHERE
STARTDATE <= TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'))
AND
ENDDATE >= TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'));
TRUNCまたはTO_CHARとTO_DATEを使用して、SYSDATEの時分秒部分をカットして比較する。
TRUNC使用方法
文法
TRUNC ( datetime [, format] )
return [ date ]
引数
datetime:切り捨てを行なう日時式(DATE 型)
format:切り捨てする時間の要素(デフォルトは'DD'のため上記例題では時分秒部分をカットする)
戻り値
切り捨てられた日付(DATE 型)
参考
・現在の日付型のフォーマットの確認方法
SQL> select sysdate from dual;
SYSDATE
--------
2020/12/03
→YYYY/MM/DD形式になっている
・接続しているセッションのみ、フォーマット形式を変更する方法(YYYY/MM/DD HH24:MI:SSに変更する場合)
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
session SETが変更されました
SQL> select sysdate from dual;
SYSDATE
-------------------
2020/12/03 12:00:00
→YYYY/MM/DD HH24:MI:SS 形式になっている