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 形式になっている