datetimeoffset (Transact-SQL)
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
datetime2 のような 24 時間制に基づいて 1 日の時刻と組み合わされる日付を定義し、世界協定時刻 (UTC) に基づいてタイム ゾーン認識を追加します。
datetimeoffset の説明
| プロパティ | 値 |
|---|---|
| 構文 | DATETIMEOFF Standard Edition T [ ( 秒の小数部の有効桁数 ) ] |
| 使用方法 | DECLARE @MyDatetimeoffset DATETIMEOFFSET(7);CREATE TABLE Table1 (Column1 DATETIMEOFFSET(7)); |
| 既定の文字列リテラル形式 (下位レベルのクライアントに使用) | yyyy-MM-dd HH:mm:ss[.nnnnnnn] [{+|-}hh:mm] 詳細については、次の「下位レベルの クライアント の下位互換性」セクションを参照してください。 |
| 日付範囲 | 0001-01-01 から 9999-12-311 月 1 日 1 CE ～12 月 31 日 9999 CE |
| 時間範囲 | 00:00:00 から 23:59:59.9999999 |
| タイム ゾーン オフセット範囲 | -14:00 から +14:00 |
| 要素範囲 | yyyyは、1 年を00019999表す 4 桁の数字です。MMは、指定した年の月を0112表す 2 桁の数字です。ddは、指定した月の日を0131表す 2 桁の数字で、月に応じて異なります。HHは、時間を0023表す 2 桁の数字です。mmは、分を0059表す 2 桁の数字です。ssは、2 番目を0059表す 2 桁の数字です。n は、秒の小数部を 09999999表す 0 ~ 7 桁の数字です。hhは、次の範囲-14+14の 2 桁の数字です。mmは、次の範囲0059の 2 桁の数字です。 |
| 文字の長さ | 最小 26 桁 (yyyy-MM-dd HH:mm:ss {+|-}hh:mm) から最大 34 (yyyy-MM-dd HH:mm:ss.nnnnnnn {+|-}hh:mm) |
| 精度、スケール | 次のテーブルを参照してください。 |
| ストレージ サイズ | 10 バイト。固定は既定値で、既定値は 100 ns 小数部の秒精度です。 |
| 精度 | 100 ナノ秒 |
| 既定値 | 1900-01-01 00:00:00 00:00 |
| Calendar | グレゴリオ暦 |
| ユーザー定義の秒の小数部の有効桁数 | はい |
| タイム ゾーン オフセットの認識と保持 | はい |
| 夏時間に対応 | いいえ |
| 指定した小数点以下桁数 | 結果 (有効桁数、小数点以下桁数) | 列長 (バイト) | 秒の小数部の有効桁数 |
|---|---|---|---|
| datetimeoffset | (34, 7) | 10 | 7 |
| datetimeoffset(0) | (26, 0) | 8 | 0 ~ 2 |
| datetimeoffset(1) | (28, 1) | 8 | 0 ~ 2 |
| datetimeoffset(2) | (29, 2) | 8 | 0 ~ 2 |
| datetimeoffset(3) | (30, 3) | 9 | 3 ~ 4 |
| datetimeoffset(4) | (31, 4) | 9 | 3 ~ 4 |
| datetimeoffset(5) | (32, 5) | 10 | 5 ~ 7 |
| datetimeoffset(6) | (33, 6) | 10 | 5 ~ 7 |
| datetimeoffset(7) | (34, 7) | 10 | 5 ~ 7 |
datetimeoffset でサポートされる文字列リテラル形式
次の表は、datetimeoffset でサポートされている ISO 8601 文字列リテラル形式を一覧にしたものです。 datetimeoffset の日付と時刻部分のアルファベット形式、数値形式、非指定形式、時刻形式については、「日付 (Transact-SQL) と時刻 (Transact-SQL)」を参照してください。
| ISO 8601 | 説明 |
|---|---|
| yyyy-MM-ddTHH:mm:ss[.nnnnnnn][{+|-}hh:mm] | これら 2 つの形式は、セッション ロケール設定のSET DATEFORMAT影響をSET LANGUAGE受けません。 datetimeoffset と datetime 部分の間にスペースを使用することはできません。 |
| yyyy-MM-ddTHH:mm:ss[.nnnnnnn]Z (UTC) | ISO 定義に基づくこの形式は、datetime 部分を協定世界時 (UTC) で指定する必要があることを示します。 たとえば、 1999-12-12 12:30:30.12345 -07:00 次のように 1999-12-12 19:30:30.12345Z表す必要があります。 |
次の例では、文字列をそれぞれの date および time データ型にキャストした結果を比較します。
SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS TIME(7)) AS 'time',
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS DATE) AS 'date',
CAST('2007-05-08 12:35:29.123' AS SMALLDATETIME) AS 'smalldatetime',
CAST('2007-05-08 12:35:29.123' AS DATETIME) AS 'datetime',
CAST('2007-05-08 12:35:29.1234567+12:15' AS DATETIME2(7)) AS 'datetime2',
CAST('2007-05-08 12:35:29.1234567 +12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset',
CAST('2007-05-08 12:35:29.1234567+12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset IS08601';
結果セットは次のようになります。
| データ型 | 出力 |
|---|---|
time |
12:35:29.1234567 |
date |
2007-05-08 |
smalldatetime |
2007-05-08 12:35:00 |
datetime |
2007-05-08 12:35:29.123 |
datetime2 |
2007-05-08 12:35:29.1234567 |
datetimeoffset |
2007-05-08 12:35:29.1234567 +12:15 |
datetimeoffset IS08601 |
2007-05-08 12:35:29.1234567 +12:15 |
タイム ゾーン オフセット
タイム ゾーン オフセットは、time または datetime 値の UTC を基準とした相対値を指定します。 タイム ゾーン オフセットは、[+|-] hh:mm として表すことができます。
hhは、タイム ゾーン オフセットの時間数を表す 2 桁の数字0014です。mmは、タイム ゾーン オフセットの0059追加分数を表す 2 桁の数字です。+(プラス) または-(マイナス) は、タイム ゾーン オフセットの必須記号です。 この記号は、現地時刻を取得するために、タイム ゾーン オフセットを UTC 時刻から加算または減算するかどうかを示します。 タイム ゾーン オフセットの有効範囲は-14:00～+14:00までです。
タイム ゾーン オフセット範囲は、XSD スキーマ定義の W3C XML 標準に従っており、SQL 2003 標準定義 12:59+14:00とは若干異なります。
fractional seconds precision は、1 秒未満の桁数を指定するオプションのパラメーターです。 この値は、0 から 7 (100 ナノ秒) の整数で指定できます。 既定 の秒の小数部の有効桁数 は 100 ns です (秒の小数部の場合は 7 桁)。
データベースに格納されたデータは、サーバーで UTC として処理、比較、並べ替え、およびインデックス化されます。 タイム ゾーン オフセットは、取得のためにデータベースに保持されます。
指定されたタイム ゾーン オフセットは、夏時間 (DST) に対応し、DST 期間内の特定 の日時 に合わせて調整されたものと見なされます。
datetimeoffset 型の場合、挿入、更新、算術、変換、または割り当て操作中に、UTC とローカル (永続的または変換されたタイム ゾーン オフセット) の両方の datetime 値が検証されます。 無効な UTC またはローカル (永続的または変換されたタイム ゾーン オフセット) の datetime 値が検出されると、無効な値エラーが発生します。 たとえば、 9999-12-31 10:10:00 UTC では有効ですが、ローカル時刻でタイム ゾーン オフセット +13:50にオーバーフローします。
タイム ゾーン変換の構文
SQL Server 2016 (13.x) では、夏時間に対応したユニバーサル タイム ゾーン変換を容易にする構文が導入 AT TIME ZONE されました。 この構文は、タイム ゾーン オフセットのないデータをタイム ゾーン オフセットを持つデータに変換する場合に特に便利です。 ターゲット タイム ゾーンの対応する datetimeoffset 値に変換するには、「AT TIME ZONE」を参照してください。
ANSI および ISO 8601 への準拠
日付と時刻の記事の ANSI および ISO 8601 コンプライアンス セクションは、datetimeoffset に適用されます。
下位クライアントの下位互換性
一部の下位レベル クライアントは、time、date、datetime2、datetimeoffset データ型をサポートしていません。 SQL Server の上位インスタンスと下位クライアントの間のデータ型マッピングを次の表に示します。
| SQL Server のデータ型 | 下位クライアントに渡される既定の文字列リテラル形式 | 下位 ODBC | 下位 OLEDB | 下位 JDBC | 下位 SQLCLIENT |
|---|---|---|---|---|---|
| time | HH:mm:ss[.nnnnnnn] | SQL_WVARCHAR または SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String または SqString |
| date | yyyy-MM-dd | SQL_WVARCHAR または SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String または SqString |
| datetime2 | yyyy-MM-dd HH:mm:ss[.nnnnnnnnn] | SQL_WVARCHAR または SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String または SqString |
| datetimeoffset | yyyy-MM-dd HH:mm:ss[.nnnnnnn] [+|-]hh:mm | SQL_WVARCHAR または SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String または SqString |
日付と時刻のデータを変換する
日付と時刻のデータ型に変換すると、SQL Server は日付または時刻として認識できないすべての値を拒否します。 日付と時刻のデータで関数をCASTCONVERT使用する方法については、CAST および CONVERT を参照してください。
datetimeoffset データ型に変換する
このセクションでは、オフセットのないデータ型から新しい datetimeoffset データ型列にデータを更新する例を示します。
最初に、sys.time_zone_info システム カタログ ビューからタイム ゾーン名を確認します。
SELECT * FROM sys.time_zone_info WHERE name = 'Pacific Standard Time';
次の例では、AT TIME ZONE 構文を 2 回使用します。 このサンプル コードでは、テーブル dbo.Auditを作成し、複数の夏時間の変更にまたがるデータを追加し、新しい datetimeoffset 列を追加します。 列が AuditCreated オフセットのない *datetime2 データ型であり、UTC タイム ゾーンを使用して書き込まれたと仮定します。
UPDATEこのステートメントでは、AT TIME ZONE構文によって最初に既存AuditCreatedの列データに UTC タイム ゾーン オフセットが追加され、そのデータが Pacific Standard TimeUTC から変換され、米国内の過去の夏時間範囲ごとの履歴データが正しく調整されます。
CREATE TABLE dbo.Audit (AuditCreated DATETIME2(0) NOT NULL);
GO
INSERT INTO dbo.Audit (AuditCreated)
VALUES ('1/1/2024 12:00:00');
INSERT INTO dbo.Audit (AuditCreated)
VALUES ('5/1/2024 12:00:00');
INSERT INTO dbo.Audit (AuditCreated)
VALUES ('12/1/2024 12:00:00');
GO
ALTER TABLE dbo.Audit
ADD AuditCreatedOffset DATETIMEOFFSET(0) NULL;
GO
DECLARE @TimeZone VARCHAR(50);
SELECT @TimeZone = [name]
FROM sys.time_zone_info
WHERE [name] = 'Pacific Standard Time';
UPDATE dbo.Audit
SET AuditCreatedOffset = AuditCreated
AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone
WHERE AuditCreatedOffset IS NULL;
GO
SELECT *
FROM dbo.Audit;
結果セットは次のようになります。
AuditCreated AuditCreatedOffset
------------------- --------------------------
2024-01-01 12:00:00 2024-01-01 04:00:00 -08:00
2024-05-01 12:00:00 2024-05-01 05:00:00 -07:00
2024-12-01 12:00:00 2024-12-01 04:00:00 -08:00
datetimeoffset データ型を他の日付と時刻型に変換する
ここでは、datetimeoffset データ型が他の日付/時刻データ型に変換される場合の処理について説明します。
日付に変換すると、年、月、日がコピーされます。 次のコードは、datetimeoffset(4) 値を日付値に変換した結果を示しています。
DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date DATE = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @date AS 'date';
結果セットは次のようになります。
@datetimeoffset date
------------------------------ ----------
2025-12-10 12:32:10.0000 +01:0 2025-12-10
変換が time(n) の場合、時間、分、秒、秒の小数部がコピーされます。 タイム ゾーンの値は切り捨てられます。 datetimeoffset(n) 値の有効桁数が time(n) 値の有効桁数より大きい場合、値は切り上げられます。 次のコードは、datetimeoffset(4) 値を time(3) 値に変換した結果を示しています。
DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @time TIME(3) = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';
結果セットは次のようになります。
@datetimeoffset time
------------------------------- ------------
2025-12-10 12:32:10.1237 +01:00 12:32:10.124
datetime に変換すると、日付と時刻の値がコピーされ、タイム ゾーンが切り捨てられます。 datetimeoffset(n) 値の小数部の有効桁数が 3 桁より大きい場合、値は切り捨てられます。 次のコードは、datetimeoffset(4) 値を datetime 値に変換した結果を示しています。
DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @datetime DATETIME = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';
結果セットは次のようになります。
@datetimeoffset datetime
------------------------------ -----------------------
2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123
smalldatetime への変換の場合は、日付と時間がコピーされます。 分は秒の値に応じて切り上げられ、秒は 0 に設定されます。 次のコードは、datetimeoffset(3) 値を smalldatetime 値に変換した結果を示しています。
DECLARE @datetimeoffset DATETIMEOFFSET(3) = '1912-10-25 12:24:32 +10:0';
DECLARE @smalldatetime SMALLDATETIME = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';
結果セットは次のようになります。
@datetimeoffset @smalldatetime
------------------------------ -----------------------
1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00
datetime2(n) への変換の場合、日付と時刻は datetime2 値にコピーされ、タイム ゾーンは切り捨てられます。 datetime2(n) 値の有効桁数が datetimeoffset(n) 値の有効桁数より大きい場合、秒の小数部は収まるように切り捨てられます。 次のコードは、datetimeoffset(4) 値を datetime2(3) 値に変換した結果を示しています。
DECLARE @datetimeoffset DATETIMEOFFSET(4) = '1912-10-25 12:24:32.1277 +10:0';
DECLARE @datetime2 DATETIME2(3) = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';
結果セットは次のようになります。
@datetimeoffset @datetime2
---------------------------------- ----------------------
1912-10-25 12:24:32.1277 +10:00 1912-10-25 12:24:32.12
文字列リテラルを datetimeoffset に変換する
文字列リテラルから日付/時刻データ型への変換は、文字列のすべての部分が有効な形式になっている場合に可能になります。 それ以外の場合は実行時エラーが発生します。 日付と時刻の型から文字列リテラルへのスタイルを指定しない暗黙的な変換または明示的な変換は、現在のセッションの既定の形式です。 次の表では、文字列リテラルを datetimeoffset データ型に変換する規則を示します。
| 入力文字列リテラル | datetimeoffset(n) |
|---|---|
ODBC DATE |
ODBC 文字列リテラルは、datetime データ型にマップされます。 リテラルから ODBC DATETIME datetimeoffset 型への代入操作では、変換規則で定義されているように、datetime とこの型の間で暗黙的な変換が行われます。 |
ODBC TIME |
前のルールを ODBC DATE 参照してください |
ODBC DATETIME |
前のルールを ODBC DATE 参照してください |
DATE のみ |
パーツの既定値00:00:00は TIME . 既定値は TIMEZONE 〗 +00:00 |
TIME のみ |
パーツの既定値1900-1-1は DATE . 既定値は TIMEZONE 〗 +00:00 |
TIMEZONE のみ |
既定値が設定される |
DATE + TIME |
既定値は TIMEZONE 〗 +00:00 |
DATE + TIMEZONE |
禁止 |
TIME + TIMEZONE |
DATE パーツの既定値は 〗〗です 1900-1-1 |
DATE + TIME + TIMEZONE |
単純変換 |
関連するコンテンツ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示