转--DATETIME数据类型

 

转自   http://blog.csdn.net/feixianxxx/archive/2009/10/15/4677124.aspx

 

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 ，转化后的时间比字符串快了大约17秒，这是因为自动舍入到最近的百分之三秒
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' --转化成2009-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 相关查询问题

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,--这里需要判断如果出生的那天是闰年的2月29日，而今年的生日是28日，那么+1天（1号过生日）
    BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
     THEN 1 ELSE 0 END AS BDNxt ,--这里需要判断如果出生的那天是闰年的2月29日，而明年的生日是28日，那么+1天（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(DAY,DT+@@DATEFIRST-N)--这里的N为你想星期几为第一条就设置几

DATEPART(DAY,DT+@@DATEFIRST-1)--大家平时的生活习惯

 

问题：如果要查某个表里所有星期2的生产数量

上面的思路做就是:

select * from tn where DATEPART(DAY,DT+@@DATEFIRST-1)=2

下面我说一个精巧的思路：

如果你知道某个具体日期的是星期 比如‘19000102’ (这里写这个原因我想是因为日期够小 方便下面减出来一定是正数)

select * from tn where datediff(day,'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;
GO
例子可以很好说明问题，不解释了

 

d.生产日期的方法

这里首先要提到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
-----------------
/*
函数生产100W连续数据
*/
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)

然后我们再来写返回2个日期间的日期列表

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：此文章是我看书总结而来 拿出来的目的是大家学习交流用 （部分代码是书上代码）不对的地方大家可以拍砖~