1.关于字符型和DATETIME类型的转化
由于时间类型的优先级高于字符型,所以当比较一个DATETIME类型的数据和一个字符串的时候,字符串首先会转化成DATETIME类型。
A '20060102' '090807' '20090203 12:11:11.111'转化成时间类型的时候
B '2009-02-03 12:11:11.111'转化成时间类型的时候
不受会话语言设置影响(SET LANGUAGE) 注意B类型转换的时候不讷讷个省略它的时间部分。
2.字符型和DATETIME类型转换精度问题
--字符串转化成datetime ,转化后的时间比字符串慢了1毫秒,这是因为自动舍入到最近的百分之三秒
declare @s varchar(100)
set @s='2005-09-23 03:23:43:001'
SELECT CAST(@s AS datetime)
/*
-----------------------
2005-09-23 03:23:43.000
*/
-------------------------
--字符串转化成smalldatetime ,转化后的时间比字符串快了大约秒,这是因为自动舍入到最近的分钟数
declare @s varchar(100)
set @s='2005-09-23 03:23:43:001'
SELECT CAST(@s AS Smalldatetime)
/*
-----------------------
2005-09-23 03:24:00
*/
------------------
还要注意 它们之间的转化并不是什么简单的截断数据 有精确到毫秒时候 毫秒部分匹配【0-9】【0-9】【037】
SQL SERVER 有时候无法精确表示某个时间的瞬间,比如‘20090202 23:59:59.999’,自动舍入为
‘20090203 00:00:00.000’
declare @s table (dates datetime)
insert @s
select '20090211 00:00:00.000' union all
select '20090212 00:00:00.000'
select * from @s
where dates between '20090211 00:00:00:000' and '20090211 23:59:59.999' --转化成-02-12 00:00:00.000
/*
dates
-----------------------
2009-02-11 00:00:00.000
2009-02-12 00:00:00.000
*/
select * from @s
where dates >='20090211' and dates< '20090212' --SARG,优化器考虑使用Index Seek
---------------
2009-02-11 00:00:00.000
*/
select * from @s
where convert(varchar(8),dates,112)='20090211'
/*
Dates
-----------------------
2009-02-11 00:00:00.000
*/
3.Datetime和SmallDatetime类型的内部存储
时间类型在存储时候被分成2日期和时间2独立部分.
日期部分存储的是和1900-01-01的天数差,时间部分存储,如果是Datetime,午夜0点后时钟周期数,一个时钟周期数为1/300秒,即3.33毫秒.如果是smalldatetime,时间部分存储的是午夜0点以后的分钟数.
我们只要将时间转换成16进制就可以很清楚的看到了。接下来演示一下:
declare @day1 datetime
set @day1=current_timestamp
--datetime
select @day1,--2010-03-07 17:29:49.637
CONVERT(varbinary(8),@day1),-- 0x00009D32012057FB
CONVERT(int,SUBSTRING(CONVERT(varbinary(8),@day1),1,4)),--40242
CONVERT(int,SUBSTRING(CONVERT(varbinary(8),@day1),5,4))--18876708*3.33/1000/60/60 =17.46
4.Datetime 相关查询问题
a.生日问题
这里只考虑特殊问题
使用DATEADD函数 为某年的2月29日加1年,下年由于不是闰年,所以系统默认是XXXX年2月28日。
很多人在非闰年庆祝2月29生日 都放在3月1日(书上所说,HOHO)
例子:我们来求每个员工离今天最近的生日。如果过了生日今天 那么返回明年的生日 否则 返回今年(环境2005)
SET NOCOUNT ON;
WITH Args1 AS
(
SELECT Name, BirthDate,
DATEDIFF(year, BirthDate, GETDATE()) AS Diff,--出生日期和今天的相差年份
CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today --今天的午夜时间
FROM dbo.Employees
),
Args2 AS
(
SELECT Name, BirthDate, Today,
DATEADD(year, Diff, BirthDate) AS BDCur,--这个是今年的生日
DATEADD(year, Diff + 1, BirthDate) AS BDNxt--这个是明年的生日
FROM Args1
),
Args3 AS
(
SELECT LastName, FirstName, BirthDate, Today,
BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
THEN 1 ELSE 0 END AS BDCur,--这里需要判断如果出生的那天是闰年的月日,而今年的生日是日,那么+1天(号过生日)
BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
THEN 1 ELSE 0 END AS BDNxt ,--这里需要判断如果出生的那天是闰年的月日,而明年的生日是日,那么+1天(号过生日)
FROM Args2
)
SELECT Name, BirthDate,
CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay--如果今年的生日还没过(比今天晚),那么就取今年的反之取下一年
FROM Args3;
b.标识星期数
大家都知道SQL默认的每个星期第一天是星期日 这样和我们的习惯相违背 这个时候会在做查询的时候非常不方便
大家可以通过会话的选项 DATEFIRST设置星期的第一天
我要说的是不依赖这个设置
有条经典的话:DATEFIRST的设置值和DATEPART函数返回的值存在反比关系。
什么意思呢?假设DATEFIRST的设置值 为F DATEPART函数返回的星期数 D 如果F增加N 那么D减小N
如果DATEFIRST 设置为1 今天是星期2 那么 DATEPART 返回2
如果DATEFIRST 设置为2 (1+1) 今天是星期2 那么 DATEPART 返回1 (2-1)
给出经典等式:
DATEPART(WEEKDAY,DT+@@DATEFIRST-N)--这里的N为你想星期几为第一条就设置几
DATEPART(WEEKDAY,DT+@@DATEFIRST-1)--大家平时的生活习惯
问题:如果要查某个表里所有星期2的生产数量
上面的思路做就是:
select * from tn where DATEPART(WEEKDAY,DT+@@DATEFIRST-1)=2
下面我说一个精巧的思路:
如果你知道某个具体日期的是星期 比如‘19000102’ (这里写这个原因我想是因为日期够小 方便下面减出来一定是正数)
select * from tn where datediff(WEEKDAY,'19000102’,DT)%7=0
这里的'19000102’起到一个参照作用.
c.按周分组
问题描述:如果让你求每周的订单数量 有人会想到用DATEPART(week,DT)来分组
这样有一个问题:对于一周跨年的日期,你会得到2个不同的周数
解决方法:
由于每周的分组因子是一样的(这里的分组因子我理解是你在一周内用DATEPART(WEEKDAY,DT)发生星期数是唯一的在这周内)
书上的意思就是 任意一条记录 减去日期的星期数+1 一定是可以返回本周的第一天日期 加上7天-日期的星期数 就是这周的最后一天了
这样就可以根据一周的第一天和最后一天分组了(相当巧妙我觉得)
SELECT od - wd + 1 AS week_start, od + 7 - wd AS week_end,
COUNT(*) AS numorders
FROM (SELECT OrderID AS oid, OrderDate AS od,
DATEPART(weekday, OrderDate + @@DATEFIRST - 1) AS wd
FROM dbo.Orders) AS D
GROUP BY od - wd + 1, od + 7 - wd;
例子可以很好说明问题,不解释了
d.计算工作日
--计算个日期之间的工作日(周末双休,不考虑特殊节日)
create function workday
(
@beg_time datetime,
@end_time datetime
)
returns int
as
begin
declare @n int
set @n=(
select 工作日=days/7*5+days%7
-case when 6 between wd and wd+days%7-1 then 1 end
-case when 7 between wd and wd+days%7-1 then 1 end
from(select datediff(day,@beg_time,@end_time)+1 as days,
datepart(weekday,@beg_time+@@datefirst-1) as wd
) k
)
return @N
end
go
declare @beg_time datetime,@end_time datetime
set @beg_time='2010-01-01'
set @end_time='2010-06-28'
--执行函数
select 工作日=dbo.workday(@beg_time,@end_time)
/*
工作日
-----------
127
*/
e.生产日期的方法
这里首先要提到2个参数临时表的方法
/*
生产一百万连续数据之临时表
*/
SET NOCOUNT ON
CREATE TABLE NUM(N INT PRIMARY KEY)
INSERT NUM VALUES(1);
DECLARE @MAX INT,@RC INT
SET @MAX=1000000
SET @RC=1
WHILE @RC*2<=@MAX
BEGIN
INSERT NUM
SELECT N+@RC FROM NUM
SET @RC=@RC*2
END
INSERT NUM
SELECT N+@RC FROM NUM WHERE N+@RC<=@MAX
SELECT * FROM NUM
go
-----------------
/*
函数生产W连续数据
*/
create function dbo.fn_nums(@n as bigint )
returns table
as
return
with
L0 as (select 1 as c union all select 1),
L1 as (select 1 as c from L0 AS A,L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A,L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A,L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A,L3 AS B),
L5 AS (SELECT 1 AS C FROM L4 AS A,L4 AS B),
NUMS AS (SELECT ROw_number() over (order by c) as n from L5)
select n from nums where n<=@n
select * from dbo.fn_nums(1000000)
然后我们再来写返回个日期间的日期列表
DECLARE @s AS DATETIME, @e AS DATETIME;
SET @s = '20060101';
SET @e = '20061231';
SELECT @s + n - 1 AS dt
FROM dbo.Nums -这里的表就是上面的临时表 你也可以用函数来替代
WHERE n <= DATEDIFF(day, @s, @e) + 1;
我今天就看了这么多,时间不够
PS:此文章是我看书总结而来 拿出来的目的是大家学习交流用 (部分代码是书上代码)不对的地方大家可以拍砖~
联系客服