方案一:datetime,8字节,保存'2010-10-14 00:00:00.000' 方案二:char(8),8字节,保存'20101014' 方案三:int,4字节,保存40463 方案四:int,4字节,保存20101014可用性递减。具体看你的需求。 CREATE TABLE #DateSample( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, date1 datetime NOT NULL, date2 char(8) NOT NULL, date3 int NOT NULL, date4 int NOT NULL ) GO INSERT INTO #DateSample VALUES( CONVERT(char(8),GETDATE(),112), CONVERT(char(8),GETDATE(),112), DATEDIFF(day,0,GETDATE()), CAST(CONVERT(char(8),GETDATE(),112) AS int) ) GO SELECT *, dt1 = date1, dt2 = CAST(date2 AS datetime), dt3 = DATEADD(day,0,date3), dt4 = CAST(CAST(date4 AS char(8)) AS datetime) FROM #DateSample GO
查询的时候你可以转换一下
用函数convert 随便你怎么转都行。
方案二:char(8),8字节,保存'20101014'
方案三:int,4字节,保存40463
方案四:int,4字节,保存20101014可用性递减。具体看你的需求。
CREATE TABLE #DateSample(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
date1 datetime NOT NULL,
date2 char(8) NOT NULL,
date3 int NOT NULL,
date4 int NOT NULL
)
GO
INSERT INTO #DateSample
VALUES(
CONVERT(char(8),GETDATE(),112),
CONVERT(char(8),GETDATE(),112),
DATEDIFF(day,0,GETDATE()),
CAST(CONVERT(char(8),GETDATE(),112) AS int)
)
GO
SELECT *,
dt1 = date1,
dt2 = CAST(date2 AS datetime),
dt3 = DATEADD(day,0,date3),
dt4 = CAST(CAST(date4 AS char(8)) AS datetime)
FROM #DateSample
GO