datetimeoffset (Transact-SQL)
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric Warehouse Microsoft Fabric SQL Database
datetime2 などの 24 時間制に基づいて 1 日の時刻と組み合わされる日付を定義し、協定世界時 (UTC) に基づいてタイム ゾーン認識を追加します。
datetimeoffset の説明
| プロパティ | 値 |
|---|---|
| 構文 | DATETIMEOFFSET [ ( fractional seconds precision ) ] |
| 使用方法 | 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 は、年を表す 0001 から 9999までの 4 桁の数字です。MM は、指定した年の月を表す、 01 から 12までの 2 桁の数字です。dd は、指定した月の日を表す、 01 から 31 までの 2 桁の数字です。HH は、 00 から 23 までの 2 桁の数字で、時間を表します。mm は、分を表す 00 から 59までの 2 桁の数字です。ss は、2 番目を表す 2 桁の 00 から 59までの範囲の 2 桁です。n は、秒の小数部を表す、 0 から 9999999までの範囲の 0 から 7 桁の数字です。hh は、 -14 から +14 までの範囲の 2 桁の数字です。mm は、 00 から 59 までの範囲の 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の日付と時刻の部分のアルファベット順、数値形式、非指定形式、および時刻形式については、「date (Transact-SQL)および time (Transact-SQL)を参照してください。
| ISO 8601 | 説明 |
|---|---|
| yyyy-MM-ddTHH:mm:ss[.nnnnnnn][{+|-}hh:mm] | これら 2 つの形式は、 SET LANGUAGE と SET DATEFORMAT セッションロケール設定の影響を受けません。 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は、00から14までの範囲の 2 桁の数字で、タイム ゾーン オフセットの時間数を表します。mmは、タイム ゾーン オフセットの追加分数を表す、00から59までの 2 桁の数字です。+(プラス) または-(マイナス) は、タイム ゾーン オフセットの必須記号です。 この記号は、現地時刻を取得するために、タイム ゾーン オフセットを UTC 時刻から加算または減算するかどうかを示します。 タイム ゾーン オフセットの有効範囲は-14:00～+14:00までです。
タイム ゾーン オフセット範囲は、XSD スキーマ定義の W3C XML 標準に従っており、12:59 SQL 2003 標準定義とは若干異なります。
fractional seconds precision は、1 秒未満の桁数を指定するオプションのパラメーターです。 この値は、0 から 7 (100 ナノ秒) の整数で指定できます。 既定の 秒の有効桁数 は 100 ns です (秒の小数部の場合は 7 桁)。
データベースに格納されたデータは、サーバーで UTC として処理、比較、並べ替え、およびインデックス化されます。 タイム ゾーン オフセットは、取得のためにデータベースに保持されます。
指定されたタイム ゾーン オフセットは、夏時間 (DST) に対応し、DST 期間内の特定の datetime に合わせて調整されたものと見なされます。
datetimeoffset型の場合、挿入、更新、算術、変換、または割り当て操作中に、UTC とローカル (永続的または変換されたタイム ゾーン オフセット) datetime値が検証されます。 無効な UTC またはローカル (永続的または変換されたタイム ゾーン オフセット) datetime 値が検出されると、無効な値エラーが発生します。 たとえば、 9999-12-31 10:10:00 は UTC で有効ですが、ローカル時刻ではタイム ゾーン オフセット +13:50にオーバーフローします。
タイム ゾーン変換の構文
SQL Server 2016 (13.x) では、夏時間に対応したユニバーサル タイム ゾーン変換を容易にする AT TIME ZONE 構文が導入されました。 この構文は、タイム ゾーン オフセットのないデータをタイム ゾーン オフセットを持つデータに変換する場合に特に便利です。 ターゲット タイム ゾーンの対応する datetimeoffset 値に変換するには、「 AT タイム ゾーンを参照してください。
ANSI および ISO 8601 への準拠
date および time の記事の 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 |
Microsoft Fabric のサポート
Microsoft Fabric では、現在、 datetimeoffset データ型の列を作成することはできませんが、 datetimeoffset を使用して、 AT TIME ZONE (Transact-SQL) 関数を使用してデータを変換できます。次に例を示します。
SELECT
CAST(CAST('2024-07-03 00:00:00' AS DATETIMEOFFSET) AT TIME ZONE 'Pacific Standard Time' AS datetime2) AS PST
Microsoft Fabric SQL データベースでは、7 桁の有効桁数を使用できますが、Fabric OneLake にミラー化されたデータのタイム ゾーンと 7 回目の 10 進数のトリミングが行われます。 この列の種類は、Fabric SQL データベースのテーブルの主キーとして使用できません。
日付と時刻のデータを変換する
日付と時刻のデータ型に変換すると、SQL Server は日付または時刻として認識できないすべての値を拒否します。 日付と時刻のデータで CAST 関数と CONVERT 関数を使用する方法については、 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 タイム ゾーン オフセットを追加してから、UTC から Pacific Standard Time にデータを変換し、米国の過去の夏時間範囲ごとの履歴データを正しく調整します。
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 データ型が他の日付/時刻データ型に変換される場合の処理について説明します。
dateに変換すると、年、月、日がコピーされます。 次のコードは、 datetimeoffset(4) 値を date 値に変換した結果を示しています。
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 のみ |
TIME パーツの既定値は00:00:00です。 TIMEZONEの既定値は+00:00 |
TIME のみ |
DATE パーツの既定値は1900-1-1です。 TIMEZONEの既定値は+00:00 |
TIMEZONE のみ |
既定値が設定される |
DATE + TIME |
TIMEZONEの既定値は+00:00 |
DATE + TIMEZONE |
不許可 |
TIME + TIMEZONE |
DATE パーツの既定値は 〗〗です 1900-1-1 |
DATE + TIME + TIMEZONE |
単純変換 |