SQLserver2005 中日期类型的使用全功略

最新推荐文章于 2021-03-12 15:43:28 发布

ReViSion

最新推荐文章于 2021-03-12 15:43:28 发布

阅读量1.1k

点赞数

分类专栏：数据库管理文章标签： sqlserver insert user null 工作 table

本文链接：https://blog.csdn.net/ReViSion/article/details/2817555

版权

数据库管理专栏收录该内容

26 篇文章 0 订阅

订阅专栏

日期类型有两种 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,birthdate,getdate()),
CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today from dbo.Employees
),
arg2
as
(
select lastname,firstname,birthdate,Today,BCur=dateadd(year,diff,birthdate),
bNext=dateadd(year,diff+1,birthdate) from arg1
),
arg3
as
(
select lastname,firstname,birthdate,Today, ---处理二月份的月底
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,firstname,birthdate,
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, usr, starttime, endtime)
VALUES('app1', 'user2', '20060212 08:30', '20060212 08:45');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '20060212 09:00', '20060212 09:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '20060212 09:15', '20060212 10:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '20060212 09:15', '20060212 09:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '20060212 10:30', '20060212 14:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '20060212 10:45', '20060212 11:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '20060212 11:00', '20060212 12:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '20060212 08:30', '20060212 08:45');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '20060212 09:00', '20060212 09:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '20060212 11:45', '20060212 12:00');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '20060212 12:30', '20060212 14:00');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '20060212 12:45', '20060212 13:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '20060212 13:00', '20060212 14:00');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '20060212 14:00', '20060212 16:30');
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '20060212 15:30', '20060212 17:00');

CREATE UNIQUE INDEX idx_app_usr_s_e_key
ON dbo.Sessions(app, usr, starttime, endtime, keycol);
CREATE INDEX idx_app_s_e ON dbo.Sessions(app, starttime, 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,usr,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,usr,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,usr,starttime,
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,
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
)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 end
from
(select days=datediff(day,@s,@e)+1,
wd=datepart(dw,@s+@@datefirst-1))a