个人学习1之-DATETIME数据类型】 .

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;