日付と時刻が希望の形式で表示されない場合や、クエリ出力が視聴者のニーズに合わない場合が多くあります。必要に応じて日付文字列をフォーマットするSQLServerの組み込み機能がいくつかありますが、文字列がSQL Serverによって解釈され、変換エラーを回避するには、適切なフォーマットにする必要があります。文字列から日付や時刻を変換しようとすると、次のエラーが発生することがあります。 「文字列から日付や時刻を変換するときに変換に失敗しました。」
図1:文字列からの日付および/または時刻の変換エラー
上記のエラーは通常、日付リテラルが適切でなく、文字列からDateTimeまたはdateに変換できない場合に発生します。このエラーはいくつかの理由で発生します。これらの理由については、ソリューションセットとともに詳しく説明します。
例1:
英国の日付と時刻の表記は、日-月-年の形式(2015年1月10日または2015年10月1日)を使用して日付を表示します。これは、フォーマットスタイル103のSQLServer組み込み機能「変換」関数を使用して実現できます。
以下の例では、指定された日付文字列の形式が間違っていることがわかります。最初に月、次に日、そして昨年を提供しますが、これは間違っており、SQL Serverで解釈できないため、エラーが発生します。 「103」日付スタイルを使用した英国スタイルの日付変換の正しい形式は「dd / mm / yyyy」です。
間違ったフォーマット:
@date_time_value varchar(100)= '10 / 16/2015 21:02:04 'を宣言し、CONVERT(datetime2、@ date_time_value、103)をUK_Date_Time_Styleとして選択します
図2:エラーが発生する間違った日付形式
正しい形式:
英国とフランス語の日付形式は、103 =“ dd / mm / yyyy”または3 =” dd / mm / yy”です。ここで103と3は日付スタイルです。
@date_time_value varchar(100)= '10 / 1/15 21:02:04 'を宣言し、Date_Time_StyleとしてCONVERT(datetime2、@ date_time_value、103)を選択します
図3:「dd / mm / yyyy」の英国/フランス語の日付スタイルを使用した正しい日付形式
@date_time_value varchar(100)= '10 / 1/15 21:02:04 'を宣言し、CONVERT(datetime2、@ date_time_value、3)をUK_Date_Time_Styleとして選択します
図4:「dd / mm / yy」の英国/フランス語の日付スタイルを使用した正しい日付形式
例2:
SQL Serverでの文字列から日付への変換では、使用されている日付や時刻の形式ではなく、スキームに受け入れられない誤った情報を保存しようとしているためにエラーが発生することがあります。
間違った日付:
次のエラーの理由は、2019年はうるう年ではないため、「2月29日」などの日付がないためです。
@date_time_valueを宣言しますvarchar(100)= '2019-02-29 21:02:04' select cast(@date_time_value as datetime2)as date_time_value
図5:2019年はうるう年ではないため、日付として2月29日がないために発生したエラー
正しいもの:
@date_time_valueを宣言しますvarchar(100)= '2019-02-28 21:02:04' select cast(@date_time_value as datetime2)as date_time_value
図6:正しい日付
ISO 8601日付形式:
日付値を操作するために多くの形式が利用可能ですが、グローバル/国際的な大衆のために働くとき、日時表現を選択することはユーザビリティの問題になる可能性があります。したがって、カルチャ固有の日付/時刻リテラルは避ける必要があります。この日付を「2018年3月8日」と見なすと、世界のさまざまな地域でさまざまな方法で解釈されます。
- 英国スタイルでは、「2018年3月8日」と解釈されます
- ヨーロッパスタイルでは、「2018年8月3日」と解釈されます
幸い、ISOによって開発された国際日付形式には1つの選択肢があります。グローバル標準のISO8601形式「YYYY-MM-DDThh:mm:ss」は、文字列リテラルの言語に依存しないオプションであり、これらすべての問題に対処します。一方、「yyyy」は年、「mm」は月、「dd」は日です。したがって、国際ISO形式の「2018年3月8日」の日付は「2018-03-08」と表記されます。したがって、ISO形式は日付表現に最適です。
@date_time_value varchar(100)= '2019-03-28 21:02:04'を宣言します。selectconvert(datetime2、@ date_time_value、126)as [yyyy-mm-ddThh:mi:ss.mmm]
図7:国際標準ISO8601の日付形式
推奨事項:
うまくいけば、この記事が、日付/時刻の値についてコミュニティで頻繁に見た混乱を和らげるのに役立つでしょう。ただし、日付をテキストタイプ(varchar、char、nvarchar、nchar、またはtext)に格納しないことをお勧めします。常に日付値をDATE、DATETIME、できればDATETIME2(より正確な)タイプの列に格納し、日付情報のフォーマットをそのままにします。データベースから取得する代わりに、ユーザーインターフェイスレイヤーに移動します。
読んだ2分