|
日期类型有两种 datetime 和 smalldatetime.
datetime的存储方式：共用8个字节来存放，前四个字节用于存放 距1900-1-1相隔的天数。
????? 后四个字节存入午夜到现在经过的时间，精确到 3又三分之一毫秒。
????? 时间跨度:从1753-01-01~9999-12-31号
?
smalldatetime：共用4个字节来存放，前2个字节用于存放 距1900-1-1相隔的天数。
??????? 后2个字节存入午夜到现在经过的时间,精确到分钟.
??????? 时间跨度:从1900-01-01~2079-6-6号?
日期格式可以由以下几个方面来影响:
?SET LANGUAGE,SET DATEFORMAT
下面两种表示方法不受set 选项的影响:
?1.[yy]yymmdd[ hh:mi[:ss][.mmm]]
?2.'yyyy-mm-ddThh:mi:ss[.mmm]' 如:'2006-02-12T14:23:05'
也有这种形式，{d '2006-02-12'}. ，与set选项无关，仅供API使用.
日期类型的精度演示如下：
?比如将'20060923 03:23:47.001' 转化为datetime类型
?select select cast('20060923 03:23:47.001' as datetime)
?得到的结果为2006-09-23 03:23:47.000,丢失了0.001的精度。
?如果转化为smalldatetime,则精度丢失的更多：
?select select cast('20060923 03:23:47.001' as smalldatetime)
?结果为2006-09-23 03:24:00,秒精度全部丢失。
要特别注意，因为datetime的精度问题，所以datetime的毫秒部分的格式应如下：
? [0-9][0-9][037] ，smalldatetime则只能到分钟.
所以要取出一整天的时间,只能这样写，
?WHERE dt BETWEEN '20060211 00:00:00.000' AND '20060211 23:59:59.997'
而不应该写成
?WHERE dt BETWEEN '20060211 00:00:00.000' AND '20060211 23:59:59.999'
? 等同于WHERE dt BETWEEN '20060211 00:00:00.000' AND '20060212 00:00:00.000'
或直接写成这样,
??WHERE dt >= '20060211 00:00:00.000' AND dt < '20060212 00:00:00.000'
下面列举几个方面的应用示例：
?1。生日问题,可以用如下的解决方案：检查今年员工是否已经过完了生日，过完后取下一年的生日，
没过完则取今年的生日.(引用northwind数据库)
with arg1
as
(
select lastname,firstname,birthdate,Diff=datediff(year,getdate()),
CAST(CONVERT(CHAR(8),GETDATE(),112) AS DATETIME) AS Today from dbo.Employees
),
?arg2
as
(
?select lastname,Today,BCur=dateadd(year,diff,birthdate),
??? bNext=dateadd(year,diff+1,birthdate) from arg1
),
arg3
as
(
?select? lastname,??---处理二月份的月底
?BCur= BCur+case when day(birthdate)=29 and day(BCur)=28 then 1 else 0 end,
?bNext= bNext+case when day(birthdate)=29 and day(bNext)=28 then 1 else 0 end
?from arg2
)
select lastname,
BirthDay=(case when BCur>=Today then BCur else BNext end) from arg3
2.时间交迭问题,表结构及测试数据如下：
?USE tempdb;
GO
IF OBJECT_ID('dbo.Sessions') IS NOT NULL
? DROP TABLE dbo.Sessions;
GO
CREATE TABLE dbo.Sessions
(
? keycol??? INT???????? NOT NULL IDENTITY PRIMARY KEY,
? app?????? VARCHAR(10) NOT NULL,
? usr?????? VARCHAR(10) NOT NULL,
? starttime DATETIME??? NOT NULL,
? endtime?? DATETIME??? NOT NULL,
? CHECK(endtime > starttime)
);
INSERT INTO dbo.Sessions(app,usr,starttime,endtime)
? VALUES('app1','user1','20060212 08:30','20060212 10:30');
INSERT INTO dbo.Sessions(app,'user2','20060212 08:45');
INSERT INTO dbo.Sessions(app,'20060212 09:00','20060212 09:30');
INSERT INTO dbo.Sessions(app,'20060212 09:15','20060212 10:30','20060212 14:30');
INSERT INTO dbo.Sessions(app,'20060212 10:45','20060212 11:30');
INSERT INTO dbo.Sessions(app,'20060212 11:00','20060212 12:30');
INSERT INTO dbo.Sessions(app,endtime)
? VALUES('app2','20060212 11:45','20060212 12:00');
INSERT INTO dbo.Sessions(app,'20060212 12:30','20060212 14:00');
INSERT INTO dbo.Sessions(app,'20060212 12:45','20060212 13:30');
INSERT INTO dbo.Sessions(app,'20060212 13:00','20060212 14:00','20060212 16:30');
INSERT INTO dbo.Sessions(app,'20060212 15:30','20060212 17:00');
CREATE UNIQUE INDEX idx_app_usr_s_e_key
? ON dbo.Sessions(app,endtime,keycol);
CREATE INDEX idx_app_s_e ON dbo.Sessions(app,endtime);
GO
??
?? 1)求出有重叠的两个时间段的组合,有交迭则表示一段的开始一定在另一段的开始时间与结束时间之间
??? 可以用如下语句来实现。
?? select app1=s1.app,usr1=s1.usr,key1=s1.keycol,start1=s1.starttime,end1=s1.endtime
,key2=s2.keycol,start2=s2.starttime,end2=s2.endtime
from Sessions s1
inner join Sessions s2
?on s1.app=s2.app and s1.usr=s2.usr
and s1.keycol<>s2.keycol
and (s1.starttime between s2.starttime and s2.endtime
or s2.starttime between s1.starttime and s1.endtime)
?? 2）如果要合并重迭项为一项，则首先要分别得到不在重迭区域的开始时间及结束时间.再分别组合，得到一个无重复的
????? 系列.
??? ?with starttime
as
(
?select distinct app,starttime from dbo.Sessions s
?where not exists
?(
??select 1 from dbo.Sessions a
??where s.app=a.app and s.usr=a.usr
??and s.starttime>a.starttime
??and s.starttime<=a.endtime
?)
),
endtime
as
(
?select distinct app,endtime from dbo.Sessions s
?where not exists
?(
??select 1 from dbo.Sessions a
??where s.app=a.app and s.usr=a.usr
??and s.endtime>=a.starttime
??and s.endtime<a.endtime
?)
)
select app,
endtime=(select min(endtime) from endtime
where starttime.app=endtime.app and starttime.usr=endtime.usr
and starttime.starttime<=endtime.endtime
)
?from starttime
?? 3)求出同时在线的人数最大值(相当于是找出每个开始时间在其他时段之间的最大个数)
with arg
as(
select app,
?num=(select count(1) from dbo.Sessions b
?where a.app=b.app and a.starttime>=b.starttime
?and a.starttime<b.endtime
?)
from (SELECT DISTINCT app,starttime
???? FROM dbo.Sessions) as a
)
select? app,最多在线人数=max(num) from arg
group by app;
也可以用子表来表示如下所示：
select? app,最多在线人数=max(num) from
(
select app,starttime
???? FROM dbo.Sessions) as a
)a
group by a.app
3.求指定日期是本星期的第几天，一般我们使用
DATEPART 来进行处理，但datepart返回的结果会受set datefirst的影响,设置不同返回值也不同，
所以可以用这种方式来避免设置值的不确定性.
DATEPART(weekday,dt + @@DATEFIRST - n)
其中n:表示你想要让@@datefirst设置的值
也可以用与指定日期的间隔天数来求得
SELECT OrderID,OrderDate
FROM dbo.Orders
WHERE DATEDIFF(day,'19000102',OrderDate) % 7 = 0;
比如说要一个指定日期的星期开始时间与星期结束时间可以这样：
declare @testdate datetime
set @testdate='2008-08-10';
select 星期开始=@testdate-datepart(dw,@testdate+@@datefirst-1)+1,
今天=convert(char(10),@testdate,120),
星期结束=@testdate-datepart(dw,@testdate+@@datefirst-1)+7
4.工作日与非工作日问题。
? 如果除了休息日外，还有其他节日，自定的休假日期的话，一般来说是添加一个存放休息日的辅助表来实现，
如果只有星期六或星期日为休息日的话，求工作日可以这样:
DECLARE @s AS DATETIME,@e AS DATETIME;
SET @s = '20080811';
SET @e = '20080823';
select days/7*5+days%7?-case when 6 between wd and wd+days%7-1 then 1 else 0 end?-case when 7 between wd and wd+days%7-1 then 1 else 0 endfrom(select days=datediff(day,@s,@e)+1,?wd=datepart(dw,@s+@@datefirst-1))a
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
