DATETIME 型を理解する

内部データ

DATETIME は、内部的には４バイト整数二つで表現されます。したがって、DATETIME 値は全部で８バイト必要とします。

はじめの４バイトは日付を表します。これは１９００年の１月１日から何日前、何日後という形で表現されます。残りの４バイトはその日の時刻を表します。これは深夜０時からの 3 + 1/3 ミリ秒単位で計測されます。

DATETIME 型の範囲は 1753 年１月１日から９９９９年の１２月３１日です。４バイトありますから本来は１７５３年より前の年号も表現可能なのですが、これはユリウス暦からグレゴリオ暦への変更による影響です。

一方、SMALLDATETIME は全部で４バイトしか使いません。２バイトは１９００年１月１日からの日付。残りの２バイトは、分単位で計った深夜０時からの時刻です。SMALLDATETIME では 1900年１月１日から２０７９年の６月６日までをサポートしています。（大雑把に１年を常に３６５日として概算してみる。２バイトで表現できる上限は 0xFFFF = 65535 日。65535 / 365 = 約 179.55。したがって 1900 + 179 = 2079 年。365日*0.55=約200日。200/30 = 6.67月）

文字列から DATETIME への変換

問題。'02/12/06' という文字列は DATETIME 型でどのように変換されるでしょうか。これはそれが解釈されるコンテキストに依存します。ひとつは言語設定、もうひとつは、日付フォーマットのオプションです。T-SQL では前者は SET LANGUAGE オプションで、後者は SET DATEFORMAT オプションで指定できます。しかし、SET によって環境設定を変更するとそのセッション全ての設定を変えてしまうことになるので、自分のコードだけではなく他のコードにも影響を及ぼす可能性が出てきます。ですから、文字列そのものだけであいまいにならないような日付指定が必要となります。

方法は二つ

(a) 曖昧さのない日付フォーマット
(b) CONVERT ファンクションにおけるスタイルの指定

(a) 曖昧さのない日付フォーマット

'[yy]yymmdd[ hh:mi[:ss][.mmm]]'

例:
2006年7月12日 '20060712'  または '060712'
2006年7月12日 午前7時30分 '20060712 0730'

(b) CONVERT ファンクションにおけるスタイルの指定

CONVERT ( DATETIME, '7/12/06', 103) のように、文字列の表記スタイルを指定する。

101 U.S. mm/dd/yyyy
102 ANSI yy.mm.dd
103 British/French dd/mm/yy
110 USA mm-dd-yy
111 JAPAN yy/mm/dd
112 ISO yymmdd

まるめの影響

DATETIME を扱うときには、"データの丸められ方" に注意を払う必要があります。通常、 10.9 という数値を INT 型に変換すると小数点以下が丸められ、10 になります。しかし DATETIME の場合、文字列が表す時刻に最も近い時刻に丸められます。前述の通り時刻の表現は 3 + 1/3 ミリ秒がベースとなる単位になるので、文字列によるミリ秒の表現パターンは [0-9][0-9][0|3|7] となります (例 120 123 127 など。122 や 129 などは丸められる)。

例
DROP TABLE Table_2
CREATE TABLE Table_2 (
 dt DATETIME NULL DEFAULT('000000'),
 sd SMALLDATETIME NULL DEFAULT('19000101')
)
GO
INSERT INTO Table_2 (dt) VALUES ('20060712 23:59:59')
INSERT INTO Table_2 (dt) VALUES ('20060712 23:59:59.999')
INSERT INTO Table_2 (dt) VALUES ( '20060712 23:59:59.998')
INSERT INTO Table_2 (dt) VALUES ('20060712 23:59:59.997')
SELECT * FROM Table_2
結果
dt                                    sd
2006-07-12 23:59:59.000   1900-01-01 00:00:00
2006-07-13 00:00:00.000   1900-01-01 00:00:00
2006-07-12 23:59:59: 997   1900-01-01 00:00:00
2006-07-12 23:59:59:997   1900-01-01 00:00:00

DATETIME を扱う関数

DATEADD  日付・時間の足し算（引き算）
DATEDIFF
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
MONTH
YEAR