DATEFROMPARTS, DATETIME2FROMPARTS関数


ITコーディネータのシュウです。

DSC_2085

今年は暖冬と言われており、スキー場では雪不足のところも結構あって心配されていますが、急に寒くなったりもしますね。暖冬というのは、暖かい日が続くという意味ではなく、平均して暖かいということなので、寒暖の差が激しい場合も多いようです。寒暖の差が激しいと、自律神経なども乱れがちになり、体調を崩しやすいそうです。
年末も近づいてきましたが、体調の管理には十分注意して、よい年を迎えたいものですね。
また、ブログを見て頂いた皆さん、この1年、どうもありがとうございました。来年も頑張りますので、よろしくお願いいたします!

<本日の題材>
DATEFROMPARTS, DATETIME2FROMPARTS関数

前回、日付の曜日を取得する関数として、SQL ServerのDATEPART関数、DATENAME関数というものを取り上げましたが、今回も日付に関連する内容です。

いろいろなシステムにおいて、年や月を指定してその期間内のデータについての処理を行うことがあると思います。そのとき、数値などで指定した年、月から日付型に変換するのにCAST関数などを使用して文字に変換してそれぞれをつなげ、最後にCONVERT関数などで日付に変換して対応するようなことを行う必要が出てくると思いますが、少し便利な関数を見つけましたので、今回はこれを取り上げてみたいと思います。(SQL Serverの場合)

その関数は、DATEFROMPARTS関数、DATETIMEFROMPARTS関数、DATETIME2FROMPARTS関数というものです。SQL Server2012から有効な関数です。

まず、DATEFROMPARTS関数についてですが、構文は、
DATEFROMPARTS (year, month, day)
引数の year, month, day のところに、それぞれ年、月、日を示す整数を設定することで、日付に変換して結果を返します。

例)
年、月、日をそれぞれ変数を持たせて値を設定し、それらの値に該当する日付を日付型で抽出します。

DECLARE
  @年 INT = 2015
, @月 INT = 12
, @日 INT = 21

SELECT DATEFROMPARTS (@年, @月, @日);

datefromparts

簡単に日付型のデータに変換できました。
これは、EXCELのDATE関数と同じようなイメージですね。

excel_date

※SQL Server 2008で上記の処理を実行すると、下記のようにエラーになります。

datefromparts_2008err

 ※また、月の引数部分を13にするとか、日の引数部分を32など日付として存在しない値を設定した場合もエラーになります。

上記を、SQL Server2012より以前の場合など、この関数を使用しないで別のやり方でやろうとすると、例えば以下のようにする方法があります。

DECLARE
 @年 INT = 2015
, @月 INT = 12
, @日 INT = 21
, @年月日 VARCHAR(8) 

SET @年月日 = CAST(@年 AS VARCHAR)+CAST(@月 AS VARCHAR)+CAST(@日 AS VARCHAR)

SELECT CONVERT(date, @年月日)

convert_date1

 ただ、月や日が1桁の場合や、年が4桁に満たないような場合には、以下のようにエラーになってしまいますので、もう一工夫必要になります。

DECLARE
  @年 INT = 2015
, @月 INT = 9
, @日 INT = 1
, @年月日 VARCHAR(8) 

SET @年月日 = CAST(@年 AS VARCHAR)+CAST(@月 AS VARCHAR)+CAST(@日 AS VARCHAR)

SELECT CONVERT(date, @年月日)

convert_date2_err

これは、上記の @年月日 が「201591」となって日付に変換しようとしてもできないためです。
ですので、以下のように、左側に「0」を詰めるかたちにする必要があります。

SET @年月日 = RIGHT('0000'+CAST(@年 AS VARCHAR), 4) + RIGHT('00'+CAST(@月 AS VARCHAR), 2) + RIGHT('00'+CAST(@日 AS VARCHAR), 2)

実際にやってみると、

convert_date3PNG

上記のようにすると、@年月日は「20150901」となるので、その後のCONVERT関数でエラーにならずに、日付に変換できました。

次に、時刻についても時間や分、秒を同様に引数で渡して datetime2型などで表示したい場合には、DATETIME2FROMPARTS関数というものもあります。
(datetime型にする場合は DATETIMEFROMPARTS関数。datetime型とdatetime2型の違いについては、以前の投稿(Oracle・SQL Server 日付型について)で記載しています)

下記に例を示します。

DECLARE
  @年 INT = 2015
, @月 INT = 12
, @日 INT = 21
, @時 INT = 16
, @分 INT = 42
, @秒 INT = 34
, @秒_小数 INT = 525

SELECT DATETIME2FROMPARTS (@年, @月, @日, @時, @分, @秒, @秒_小数, 3);

datetime2fromparts

指定した値を元に、datetime2型で表示されているのがわかります。
構文は、

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)

引数については下記:
year     :年を指定する整数式。
month    :月を指定する整数式。
day      :日を指定する整数式。
hour     :時間を指定する整数式。
minute   :分を指定する整数式。
seconds  :秒を指定する整数式。
fractions:小数部分を指定する整数式。
precision:返される datetime2 値の有効桁数を指定する整数リテラル。

さて、ORACLEで同様のことをする場合、下記のように、それぞれの数値を一旦TO_CHAR関数で文字でつなげた後に、日付型(今回はミリ秒があるのでTIMESTAMP型)にTO_TIMESTAMP関数で変換するなどの処理が必要になります。

※今回は、TIMESTAMP型に変換した後、SQL Serverのときと同様の形式で結果を表示したかったので、最後にTO_CHAR関数で書式を整えています。

DECLARE
  V_年 INT := 2015;
  V_月 INT := 9;
  V_日 INT := 1;
  V_時 INT := 16;
  V_分 INT := 42;
  V_秒 INT := 34;
  V_秒_小数 INT := 525;
  V_日付文字 VARCHAR2(30);
  V_日付 TIMESTAMP;
  V_日付変換 VARCHAR2(30);

BEGIN
  V_日付文字 := TRIM(TO_CHAR(V_年,'0999'))||TRIM(TO_CHAR(V_月,'09'))||TRIM(TO_CHAR(V_日,'09'))||TRIM(TO_CHAR(V_時,'09'))||TRIM(TO_CHAR(V_分,'09'))||TRIM(TO_CHAR(V_秒,'09'))||TRIM(TO_CHAR(V_秒_小数,'099'));

  DBMS_OUTPUT.PUT_LINE(V_日付文字); 

  SELECT TO_TIMESTAMP(V_日付文字, 'YYYY-MM-DD HH24:MI:SS FF3') INTO V_日付 FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(V_日付);

  SELECT TO_CHAR(V_日付, 'YYYY-MM-DD HH24:MI:SS.FF3') INTO V_日付変換 FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(V_日付変換);
END;
/

oracle_timestamp2

数値で設定した年、月、日、時間、分、秒、ミリ秒の値を元に、V_日付という TIMESTAMP型の変数に変換された値が設定されていることが確認できます。

※月で1桁のものを2桁の文字で表示する場合などに、書式としてTO_CHAR(項目, ‘09’)というようにすると、2桁に足らない場合には、頭に「0」を埋めてくれます。

また、ミリ秒を設定するときの書式は、「FF[1-9]」というものがあり、今回の小数点以下3桁の場合は、「FF3」と設定します。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>