文字列を日付に変換するSQL Server関数

2022-02-16 14:10:36

生データを扱っていると、テキストとして保存されている日付の値を頻繁に目にすることがあります。分析時には日付の方が価値があるため、これらの値を日付データ型に変換することは非常に重要です。SQL Server では、文字列を日付に変換するために、さまざまな方法を使用できます。

生データを扱う場合、テキストとして保存されている日付の値に遭遇することがよくあります。分析時には日付の方が価値が高い場合があるため、これらの値を日付データ型に変換することは重要です。SQL Server では、文字列を日付に変換するさまざまな方法があります。

一般に、データ型の変換には2つのタイプがあります。

一般に、データ型変換には2つのタイプがあります。

暗黙の 変換がユーザーに見えない場合、データを読み込む際に関数を使わずにデータ型が変更される場合。

暗黙の 変換がユーザーに見えない場合、関数を使用せずにデータをロードする際にデータ型を変更する。
明示的 変換がユーザーに見える状態で、CAST関数やCONVERT関数、その他のツールを使って実行される場合。

明示的 ユーザーから見える変換を表示し、CAST関数やCONVERT関数などを使って変換を実行することができる

今回は、SQL ServerでCAST()、TRY_CAST()、CONVERT()、TRY_CONVERT()、TRY_PARSE()などの組み込み関数を使って暗黙的に、または明示的に文字列から日付への変換を実現する方法を説明します。

この記事では、CAST(), TRY_CAST(), CONVERT(), TRY_CONVERT(), TRY_PARSE() などの組み込み関数を使用して、SQL Server で文字列から日付への変換を暗黙的または明示的に実装する方法について説明します。

備考 : 始める前に、使用されているSQL文のいくつかは、データコンテキストの観点からは無意味であり、概念を説明するために使用されているだけであることに留意してください。

備考 : 始める前に、使用されているSQL文のいくつかは、データコンテキストの観点からは無意味であり、概念を説明するためにのみ使用されていることに注意してください。

SQL Serverです。暗黙のうちに文字列を日付に変換する ( SQL Server: 暗黙のうちに文字列を日付に変換する )

上記のように、データ型を暗黙的に変換することは、ユーザーには見えません。例えば、異なるデータ型を持つ2つのフィールドまたは値を比較する場合です。

上記のように、データ型を暗黙的に変換することは、異なるデータ型を持つ2つのフィールドまたは値を比較する場合の例として、ユーザーからは見えません。

SELECT * FROM information_schema.columns where '1' = 1

SQL Server では、文字列から日付への変換は、文字列の日付形式とデフォルトの言語設定 (地域設定) に暗黙的に依存します。 yyyyMMdd または yyyy-MM-ddTHH:mm:ss(.mmm)。 地域設定に関係なく変換できますが、サポートされている形式でなければ例外が発生します。例えば、地域設定 "EN-US" の下で作業しているときに dd/MM/yyyy という書式で変換しようとすると、MM/dd/yyyyという書式がサポートされているため、失敗します。

SQL Serverでは、文字列から日付への変換は、文字列の日付形式とデフォルトの言語設定（ロケール設定）に暗黙的に依存します。デフォルト値は0です。文字列に格納された日付がISO形式の場合 yyyyMMdd または yyyy-MM-ddTHH:mm:ss（.mmm）であれば はロケールに関係なく動作しますが、そうでない場合は例外が発生します。たとえばロケール "EN-US" で作業しているときに文字列を dd / MM / yyyy の場合、MM / dd / yyyy 形式に変換しようとするため、失敗します。サポートされています。

SELECT * FROM information_schema.columns where GETDATE() > '13/12/2019'

以下のような例外が発生します。

次のような例外が発生します。

Msg 242, Level 16, State 3, Line 1
varchar データ型から datetime データ型への変換で、範囲外の値が発生しました。

メッセージ 242、レベル 16、ステータス 3、行 1
varchar データ型から datetime データ型への変換で、範囲外の値が発生しました。

画面はイメージです。

スクリーンショットをご覧ください。

しかし、曜日と月の部分を入れ替えると成功します。

SELECT * FROM information_schema.columns where GETDATE() > '13/12/2019'

スクリーンショットです。

スクリーンショットをご覧ください。

こちらの公式ドキュメントをご覧ください。こちら SQL Server の言語設定を変更する方法の詳細については、こちらを参照してください。

あなたはここでこの公式ドキュメントを見るは、その SQL Server の言語設定を変更する方法の詳細については、こちらをご覧ください。

さらに、SQL Serverで日付の型を暗黙的に変換する方法については、こちらの記事を参照してください。 SQL Serverにおける暗黙の変換 .

また、SQL Serverにおける日付型の暗黙の変換については、こちらの記事をご参照ください。 SQL Serverにおける暗黙の変換 .

SQL Server: 文字列を日付に明示的に変換する ( SQL Serverです。文字列を明示的に日付に変換する )

データ型を変換するための2つ目のアプローチは、いくつかの関数やツールを使って行う明示的な変換です。CAST() および PARSE() 関数を使用します。

データ型の変換方法の2つ目は明示的な変換で、特定の関数やツールを使って行う。SQL Server では、CONVERT() を使用して文字列を明示的に日付に変換することができます。CAST() 関数と PARSE() 関数です。

キャスト() ( CAST() )

CAST() は、SQL Server が提供する最も基本的な変換関数です。この関数は、与えられた値を指定されたデータ型に変換しようとします(データ型の長さは指定することのみ可能です)。

例 :

例


SELECT CAST('12/01/2019' as date) as StringToDate , CAST(GETDATE() as VARCHAR(50)) as DateToString

結果 :

成果 : は

SQL Serverでは、CAST()関数による文字列から日付への変換は、前項で述べたように暗黙の変換と同様に言語設定に依存するので、ISO形式や現在の言語設定でサポートされている形式しか変換できないことに注意しましょう。

SQL Serverでは、CAST()関数による文字列から日付への変換は、前のセクションで述べたように暗黙の変換と同様に言語設定に依存するので、現在の言語設定によってISO形式またはサポートされる形式のみを変換できることに注意してください。

変換() ( CONVERT() )

CONVERT()関数は、変換スタイルを指定できるため、CAST()よりも高度な関数です。この関数は3つの引数を取ります。(1) 希望するデータ型、( 2) 入力値、( 3) スタイル番号(オプション)。

CONVERT()関数は、変換スタイルを指定できるため、CAST()よりも高度な関数です。この関数は3つの引数を取ります。(1) 希望するデータ型、(2) 入力値、(3) スタイル番号 (オプション)。

スタイル番号が渡されない場合、CAST()関数と同様に動作します。例えば、スタイル番号を指定せずに "13/12/2019" の値を日付に変換しようとすると、現在の言語設定ではサポートされていないため、失敗します。

スタイル番号が関数に渡されない場合、動作はCAST()関数と同様です。ただし、スタイル引数が渡された場合は、そのスタイルに基づいて値を変換しようとします。例えば、スタイル番号を指定せずに値 "13/12/2019" を日付に変換しようとすると、現在の言語設定がその値をサポートしていないため、失敗します。


SELECT CONVERT(DATETIME,'13/12/2019')

結果

しかし、スタイル番号として103を渡すと、（103は dd/MM/yyyy 日付の書式）、成功します。

しかし、スタイル番号として 103 を渡すと（103 は dd / MM / yyyy 日付の書式）、成功します。


SELECT CONVERT(DATETIME,'13/12/2019',103)

結果

CONVERT()関数と日付スタイル番号の詳細については、以下の記事を参照してください。

SQL 変換の日付関数とフォーマット

SQL 日付変換の関数とフォーマット
文字列からdatetimeに変換するには？

文字列をdatetimeに変換するには？

PARSE() ( PARSE() )

PARSE() は、.Net フレームワークの Parse() 関数を使用する SQL CLR 関数です。PARSE() の構文は次のとおりです。

PARSE() は、SQL CLR 関数で、. PARSE() の構文は次のとおりです。

PARSE(<value> AS <data type> [USING <culture>])

カルチャ情報が指定されていない場合、PARSE() は CAST() 関数と同様に動作しますが、カルチャが式の中で渡された場合、この関数は次のことを試みます。たとえば、もし私たちが 13/12/2019 はデフォルトの言語設定ではサポートされていないため、失敗します。

ロケール情報が指定されていない場合、PARSE() は CAST() 関数と同じように動作しますが、式の中でロケールが渡されると、そのロケールを使用して値を目的のデータ型に変換しようと試みます。たとえば、次のようなパースを行おうとすると 13/12/2019 の値は、デフォルトの言語設定が "dd / MM / yyyy" をサポートしていないため、失敗します。

しかし、culture（アラビア語-レバノン語）として "AR-LB" を渡すと、 "dd/MM/yyyy" がサポートされ、変換に成功します。

しかし、カルチャー（アラビア語 - レバノン）として "AR-LB" を渡すと、 "dd / MM / yyyy" がサポートされている場合、変換に成功します。

TRY_CAST()、TRY_CONVERT()、TRY_PARSE() ( TRY_CAST()、TRY_CONVERT()、TRY_PARSE() )

データ型変換関数の主な問題の1つは、誤った値を扱えないことです。 "01/01/0000" のような値は変換できず、データ変換例外がスローされます。

データ型変換関数の主な問題の1つは、不正確な値を扱えないことです。例えば、多くの場合、"01/01/0000" のような不正確な日付値に遭遇します。これらの値は変換できず、データ変換例外がスローされます。

この問題を解決するには、TRY_CAST()、TRY_CONVERT()、TRY_PARSE() 関数を使用して、値が変換可能かどうかをチェックし、可能な場合は変換結果を返し、不可能な場合は NULL 値を返します。

この問題を解決するには、TRY_CAST()、TRY_CONVERT()、TRY_PARSE()関数を使って、値が変換可能かどうかをチェックし、可能な場合は変換結果を返し、そうでない場合はNULL値を返します。

例


SELECT TRY_CAST('01/01/2000' as date), TRY_CAST('01/01/0000' as date)

結果

CAST() vs CONVERT() vs PARSE() ( CAST() vs CONVERT() vs PARSE() )

これらの変換関数の違いを理解し、どのシナリオでどの関数を使うかを決めるには、以下を参考にするとよいでしょう。サイト .

これらの変換機能の違いを理解し、どのような場合にどの機能を使用するかを判断するためには、以下のサイトをご覧ください。サイト .

結論 ( 結論 )

今回は、データ変換のアプローチ全般について説明しました。そして、SQL Serverを使いながら、これらの方法を用いて、文字列を日付に変換する方法を紹介しました。SQL Serverが提供するシステム機能について、いくつかの例と、より詳細な情報を提供する外部リンクを紹介しながら説明しました。

今回は、データ変換の方法全般を紹介します。そして、これらの方法を用いて、SQL Serverを使用する際に、文字列を日付に変換する方法を紹介します。SQL Serverが提供するシステム機能について、いくつかの例とより詳細な情報を提供する外部リンクを提供しながら説明します。

<ブロッククオート

Translated from : https://www.sqlshack.com/sql-server-functions-for-converting-string-to-date/