TO_TIMESTAMP (SQL)

dateString

dateString 引数は、タイムスタンプに変換される日付文字列を指定する文字列式です。dateString には、日付値、時刻値、またはその両方を含めることができます。

dateString の各文字は、以下の例外を除いて format 文字列に対応している必要があります。

• 区切り文字のない dateString の場合を除き、先頭のゼロは指定しても省略してもかまいません。

• 年は 2 桁または 4 桁で指定できます。

• 月の省略形 (format MON) は、そのロケールでの月の省略形と一致している必要があります。ロケールによっては、月の省略形は、月の名前の先頭の連続文字でない場合があります。月の省略形では、大文字と小文字は区別されません。

• 月の名前 (format MONTH) は、完全な月名として指定する必要があります。ただし、TO_TIMESTAMP では、完全な月名が MONTH の format と一致する必要はありません。これは完全な月名の最初の文字を受け取り、月のリストからその先頭の文字シーケンスに対応する最初の月を選択します。そのため、英語では、"J" = "January"、"Ju" = "June"、"Jul" = "July" となります。指定されるすべての文字は、完全な月名の連続文字と一致する必要があります。完全な月名を逸脱した文字はチェックされません。例えば、"Fe"、"Febru"、および "FebruaryLeap" はすべて有効な値ですが、"Febs" は無効な値です。月の名前では、大文字と小文字は区別されません。

• 時刻値は、該当するロケール用に定義されている時刻区切り文字を使用して指定できます。出力タイムスタンプは必ず、ODBC 標準時刻区切り文字のコロン (:) とピリオド (.) で時刻値を表します。時、分、秒にはコロン (:)、秒の小数部にはピリオド (.) が使用されます。時刻要素を省略すると、既定で 00:00:00 が使用されます。既定では、秒の小数部のないタイムスタンプが返されます。

dateString でタイムスタンプのコンポーネントを省略している場合、TO_TIMESTAMP が不足するコンポーネントを提供します。

• 時刻を指定せずに日付を指定すると、TO_TIMESTAMP は、返される時刻値に 00:00:00 を設定します。時刻の一部のみを省略すると、TO_TIMESTAMP は、その部分に 00 を設定します。秒の小数部はサポートされますが、明示的に指定する必要があります。

• 日付なしで時刻を指定すると、TO_TIMESTAMP は、返される日付値を現在の年の 01-01 (1 月 1 日) に設定します。日付のみを省略すると、日付は既定で 01 となります。月も省略すると、月と日付が既定で 01-01 となります。

• 年を省略すると、既定で 00 になります。これは、format の年要素に応じて、4 桁の年に拡張されます。format でも年を省略すると、YYYY が既定で現在の年になります。

TO_TIMESTAMP の日付は、0001 年 1 月 1 日から 9999 年 12 月 31 日までを指定できます。それより前の日付を表すには、TO_DATE 関数を使用します。

format

format 引数は、dateString の形式を指定する日付/時刻文字列です。TO_TIMESTAMP は、format の対応する位置にある形式要素に基づいて、dateString の日付要素を変換します。format の要素は以下のルールに従う必要があります。

• 形式要素では、大文字と小文字は区別されません。

• 形式要素をどのような順序で、またはいくつ使うかについては、たいていの場合制限はありません。

• 形式文字列は、dateString の区切り文字に一致する、英数字でない区切り文字 (スペース、スラッシュ、ハイフンなど) で要素を区切ります。これらの区切り文字は出力文字列には表示されません。出力文字列では標準のタイムスタンプ区切り文字であるハイフン (日付値)、コロン (時刻値)、および秒の小数部がある場合は必要に応じてピリオドが使用されます。この区切り文字の使用は、NLS (各国言語サポート) ロケールに対して定義されている DateSeparator に依存しません。

• 日付形式文字列 MMDDYYYY、DDMMYYYY、YYYYMMDDHHMISS、YYYYMMDDHHMI、YYYYMMDDHH、YYYYMMDD、YYYYDDMM、HHMISS、および HHMI には、区切り文字は必要ありません。不完全な日付形式 YYYYMM も使用でき、DD 値は 01 と見なされます。これらの形式では、最後の要素を除き、すべての要素で先頭のゼロを指定します。

• format の無効な形式の要素は無視されます。

以下の表は、有効な日付と時刻の形式要素を示しています。大文字の形式が示されていますが、これらの要素では大文字と小文字が区別されません。

TO_TIMESTAMP format には、D (曜日番号)、DY (曜日の省略形)、または DAY (曜日名) 要素を含めることもできます。ただし、これらの形式要素は、検証されることや戻り値の決定に使用されることはありません。これらの format 要素の詳細は、"TO_CHAR" を参照してください。

format を省略すると、TO_TIMESTAMP は既定の形式 DD MON YYYY HH:MI:SS を使用して日付文字列を解析します。例えば、'01 Feb 3456 07:08:09' のようになります。

日付文字列から複数のタイムスタンプ形式への変換

以下の文は、さまざまな形式で日付文字列を指定します。最初の例は既定の形式を使用し、それ以外は TO_TIMESTAMP が日付文字列を解析するために使用するフォーマット引数を指定します。TO_TIMESTAMP は、これらすべての日付文字列をタイムスタンプ 2022–06–29 00:00:00 に変換します。

SELECT
  TO_TIMESTAMP('29 JUN 2022'),
  TO_TIMESTAMP('2022 Jun 29','YYYY MON DD'),
  TO_TIMESTAMP('JUNE 29, 2022','month dd, YYYY'),
  TO_TIMESTAMP('2022***06***29','YYYY***MM***DD'),
  TO_TIMESTAMP('06/29/2022','MM/DD/YYYY'),
  TO_TIMESTAMP('29/6/2022','DD/MM/YYYY')

以下の文は、YYYYMM 日付形式を指定します。これは要素セパレータを必要としません。TO_TIMESTAMP は、欠落している日付値と時刻値を補い、タイムスタンプ 2022–06–01 00:00:00 を返します。

SELECT TO_TIMESTAMP('202206','YYYYMM')

以下の文は、HH:MI:SS.FF 時刻形式のみを指定します。TO_TIMESTAMP は、欠落している日付値を補い、どのような場合でも YYYY–01–01 (YYYY は現在の年) の日付値を返します。時刻値は、日付文字列で指定された秒の小数部によって異なります。TO_TIMESTAMP は、秒の小数部を指定どおりに渡し、埋め込みや切り捨ては行いません。

SELECT TO_TIMESTAMP('11:34','HH:MI:SS.FF'),
  TO_TIMESTAMP('11:34:22','HH:MI:SS.FF'),
  TO_TIMESTAMP('11:34:22.00','HH:MI:SS.FF'),
  TO_TIMESTAMP('11:34:22.7','HH:MI:SS.FF'),
  TO_TIMESTAMP('11:34:22.7000000','HH:MI:SS.FF')

以下の文では、秒の小数部が含まれる時刻形式を指定するための他の方法を示しています。これら 3 つの TO_TIMESTAMP の呼び出しはすべて、時刻部分の値が 11:34:22.9678 である ODBC 形式のタイムスタンプを返します。最初の 2 つの呼び出しでは、省略された日付部分は、既定で現在の年の 1 月 1 日になります。3 番目の呼び出しは、日付部分を指定します。

SELECT TO_TIMESTAMP('113422.9678','HHMISS.FF'),
       TO_TIMESTAMP('9678.113422','FF.HHMISS'),
       TO_TIMESTAMP('9678.20220629113422','FF.YYYYMMDDHHMISS')

既定のタイムスタンプ列値の設定

TO_TIMESTAMP は、テーブルの列に既定のタイムスタンプ値を指定できます。例えば、以下の文は、TIMESTAMP のタイプの列である ReviewDate の既定値を受け入れるテーブルを作成します。

CREATE TABLE Sample.MyEmpReviews (
  EmpNum INTEGER UNIQUE NOT NULL,
  ReviewDate TIMESTAMP DEFAULT TO_TIMESTAMP(365,'DDD'))

ReviewDate の値を指定せずに行を挿入すると、ReviewDate は、現在の年の365 日目の既定のタイムスタンプに設定されます。

INSERT INTO Sample.MyEmpReviews (EmpNum) VALUES (1)

TO_TIMESTAMP を使用すると、CREATE TABLE と ALTER TABLE ADD COLUMN の両方の文に既定の列値を設定できます。これらの既定値を設定する場合、dateString はリテラル値である必要があります。

2 桁の年の変換 (RR および RRRR 形式)

YY 形式では、2 桁の年の値に 19 が付加されて 4 桁の値に変換されます。例えば、07 は 1907 になり、93 は 1993 になります。RR 形式および RRRR 形式では、2 桁の年から 4 桁の年への変換をより柔軟に行います。

RR 形式の変換は現在の年に基づきます。

• 現在の年が、ある世紀の前半にある場合、以下のようになります。

  • 00 ～ 49 までの 2 桁の年は現在の世紀の 4 桁の年に展開されます。

  • 50 ～ 99 までの 2 桁の年は前の世紀の 4 桁の年に展開されます。

  以下の文は、現在の年が 2000 から 2050 までの間の場合に TO_TIMESTAMP が返すタイムスタンプの表示形式を示しています。

  SELECT
    TO_TIMESTAMP('29 September 00','DD MONTH RR'), -- 2000-09-29 00:00:00
    TO_TIMESTAMP('29 September 18','DD MONTH RR'), -- 2018-09-29 00:00:00
    TO_TIMESTAMP('29 September 49','DD MONTH RR'), -- 2049-09-29 00:00:00
    TO_TIMESTAMP('29 September 50','DD MONTH RR'), -- 1950-09-29 00:00:00
    TO_TIMESTAMP('29 September 77','DD MONTH RR')  -- 1977-09-29 00:00:00

  

• 現在の年が、ある世紀の後半にある場合、すべての 2 桁の年が現在の世紀の 4 桁の年に展開されます。

  以下の文は、現在の年が 2050 から 2099 までの間の場合に TO_TIMESTAMP が返す日付の表示形式を示しています。

  SELECT
    TO_TIMESTAMP('29 September 00','DD MONTH RR'), -- 2000-09-29 00:00:00
    TO_TIMESTAMP('29 September 21','DD MONTH RR'), -- 2021-09-29 00:00:00
    TO_TIMESTAMP('29 September 49','DD MONTH RR'), -- 2049-09-29 00:00:00
    TO_TIMESTAMP('29 September 50','DD MONTH RR'), -- 1950-09-29 00:00:00
    TO_TIMESTAMP('29 September 77','DD MONTH RR')  -- 1977-09-29 00:00:00

  

RRRR 形式を使用することにより、2 桁と 4 桁の年を混合して入力することができます。TO_TIMESTAMP は、4 桁の年を変更せずに渡します。TO_TIMESTAMP は、この例で前述の RR 形式アルゴリズムを使用して、2 桁の年を 4 桁の年に変換します。

以下の文は、現在の年が 2000 から 2050 までの間の場合に TO_TIMESTAMP が返す日付の表示形式を示しています。

SELECT 
  TO_TIMESTAMP('29 September 2021','DD MONTH RRRR'), -- 2021-09-29 00:00:00
  TO_TIMESTAMP('29 September 21','DD MONTH RRRR'),   -- 2021-09-29 00:00:00
  TO_TIMESTAMP('29 September 1949','DD MONTH RRRR'), -- 1949-09-29 00:00:00
  TO_TIMESTAMP('29 September 49','DD MONTH RRRR'),   -- 2049-09-29 00:00:00
  TO_TIMESTAMP('29 September 1950','DD MONTH RRRR'), -- 1950-09-29 00:00:00
  TO_TIMESTAMP('29 September 50','DD MONTH RRRR')    -- 1950-09-29 00:00:00

年間通算日の変換 (DDD 形式)

DDD 形式を使用して、年間通算日 (1 月 1 日から経過した日数) を実際のタイムスタンプに変換できます。この変換を実行するには、以下の点に留意してください。

• format 引数の日付部分には、DDD 形式要素を含める必要があります。また、オプションで YYYY、YY、RR、RRRR などの年形式を含めることができます。これらの要素は任意の順序で指定できますが、要素の間に区切り文字を追加する必要があります。年を省略した場合、TO_TIMESTAMP によって現在の年が既定値として使用されます。

• dateString 引数には、対応する day および year の値を含める必要があります。

  • day は、1 から 365 までの整数です (year がうるう年の場合は 366)。

  • year は、0001 から 9999 の範囲の年です。

以下の文は、2022 年の 60 日目を返します。

SELECT TO_TIMESTAMP('2022:60','YYYY:DDD') --2022-03-01 00:00:00

TO_TIMESTAMP は月の要素を変更せずに渡します。形式文字列に DD と DDD 要素の両方が含まれる場合、TO_TIMESTAMP は DDD 要素を処理し、DD 要素を無視します。例えば、以下の文は、2020 年 12 月 31 日ではなく、2020 年 2 月 29 日 (2020 年の 60 日目) のタイムスタンプを返します。

SELECT TO_TIMESTAMP('2020-12-31-60','YYYY-MM-DD-DDD')

TO_TIMESTAMP は、年間通算日そのものではなく、年間通算日を含むタイムスタンプ式を返します。この日付の値を返すには、TO_CHAR を使用します。

関連する SQL 関数

• TO_TIMESTAMP は、フォーマットされた日付/時刻文字列を標準のタイムスタンプに変換します。

• TO_CHAR は、逆の操作を実行します。これは標準のタイムスタンプをフォーマットされた日付/時刻文字列に変換します。

• TO_DATE は、フォーマットされた日付文字列を日付整数に変換します。

• CAST および CONVERT は、TIMESTAMP データ型の変換を実行します。