求sql：把字符串转为时间，得到8小时前的记录

Question

表Car
字段CookiesId(nvarchar(50))  20130205113721
　　　　　　　　　　　　　　 20130208102212
　　　　　　　　　　　　　　 20130211095407
　　　　　　　　　　　　　　 20130212225649
　　　　　　　　　　　　　　 20130214210708
　　　　　　　　　　　　　　 20130215133752 等等等求：时间差，需要将字符串类型转换为时间，以当前时间为准，得到8小时前的记录

Answer 1

where datediff(hh,rtrim(cast(left(CookiesId,8) as date))+' '+
                  substring(CookiesId,9,2)+':'+substring(CookiesId,11,2)+':'+
                  right(CookiesId,2),getdate())>8

Answer 2

一定要用substring吗，不是有个cast .... as DateTime 可以转成日期么？但是我不会用，不知道可以这样写吗？

Answer 3

 cast .... as DateTime  是有的 但是你的CookiesId格式不对

Answer 4

where CookiesId<convert(char(8),dateadd(hour,-8,getdate()),112)+replace(convert(char(19),dateadd(hour,-8,getdate()),108),':','')

Answer 5

1、没必要用nvarchar，非要用字符串的话使用varchar即可。
2、convert、cast都可以把字符串转换成datetime类型。但是前提是字符串已经是可转换的格式，否则需要先拆分并组合成可转换的格式。

Answer 6

CREATE FUNCTION dbo.fn_ConvertToDate
(
@DateStr varchar(50),
@Format varchar(50) = 'yyyy-mm-dd hh:nn:ss'
)
RETURNS DATETIME
AS 
BEGIN
DECLARE @Result DATETIME
IF(@DateStr IS NULL OR LEN(@DateStr) = 0)
BEGIN
RETURN;
END
IF(@Format = 'yyyy-mm-dd hh:nn:ss')
BEGIN
GOTO LABLE_CONVERT;
END
DECLARE @Start INT,
@Length INT,
@Year varchar(4),
@Month varchar(2),
@Day varchar(2),
@Hour varchar(2),
@Miniute varchar(2),
@Sencond varchar(2)
--获取年份
SELECT @Start = CHARINDEX('yyyy',@Format);
IF(@Start > 0)
BEGIN
SET @Length = 4;
END
ELSE
BEGIN
SELECT @Start = CHARINDEX('yy',@Format);
SET @Length = 2;
END
IF(@Start > 0)
SELECT @Year = SUBSTRING(@DateStr,@Start,@Length)
--获取月份
SELECT @Start = CHARINDEX('mm',@Format);
IF(@Start > 0)
BEGIN
SET @Length = 2;
END
ELSE
BEGIN
SELECT @Start = CHARINDEX('m',@Format);
SET @Length = 1;
END
IF(@Start > 0)
SELECT @Month = SUBSTRING(@DateStr,@Start,@Length)
--获取日期
SELECT @Start = CHARINDEX('dd',@Format);
IF(@Start > 0)
BEGIN
SET @Length = 2;
END
ELSE
BEGIN
SELECT @Start = CHARINDEX('d',@Format);
SET @Length = 1;
END
IF(@Start > 0)
SELECT @Day = SUBSTRING(@DateStr,@Start,@Length)
--获取小时
SELECT @Start = CHARINDEX('hh',@Format);
IF(@Start > 0)
BEGIN
SET @Length = 2;
END
ELSE
BEGIN
SELECT @Start = CHARINDEX('h',@Format);
SET @Length = 1;
END
IF(@Start > 0)
SELECT @Hour = SUBSTRING(@DateStr,@Start,@Length)
--获取分钟
SELECT @Start = CHARINDEX('nn',@Format);
IF(@Start > 0)
BEGIN
SET @Length = 2;
END
ELSE
BEGIN
SELECT @Start = CHARINDEX('n',@Format);
SET @Length = 1;
END
IF(@Start > 0)
SELECT @Miniute = SUBSTRING(@DateStr,@Start,@Length)
--获取秒钟
SELECT @Start = CHARINDEX('ss',@Format);
IF(@Start > 0)
BEGIN
SET @Length = 2;
END
ELSE
BEGIN
SELECT @Start = CHARINDEX('s',@Format);
SET @Length = 1;
END
IF(@Start > 0)
SELECT @Sencond = SUBSTRING(@DateStr,@Start,@Length)
SET @DateStr = 'yyyy-mm-dd hh:nn:ss'
SELECT @DateStr = REPLACE(@DateStr,'yyyy',ISNULL(@Year,'1900'))
SELECT @DateStr = REPLACE(@DateStr,'mm',ISNULL(@Month,'01'))
SELECT @DateStr = REPLACE(@DateStr,'dd',ISNULL(@Day,'01'))
SELECT @DateStr = REPLACE(@DateStr,'hh',ISNULL(@Hour,'00'))
SELECT @DateStr = REPLACE(@DateStr,'nn',ISNULL(@Miniute,'00'))
SELECT @DateStr = REPLACE(@DateStr,'ss',ISNULL(@Sencond,'00'))
LABLE_CONVERT:
SELECT @Result = CAST(@DateStr AS DATETIME);
RETURN @Result;
END
GO
/* --test:SELECT dbo.fn_ConvertToDate('20121115141552','yyyymmddhhnnss')
*/

Answer 7

字段我是一定要用字符串的，请问该保存为怎么样的字符串才能使用cast...as DateTime 直接转换，能否给个列子，谢谢

Answer 8

用字符串当然也没问题，这里是例子：
DECLARE @a VARCHAR(30)
SET @a='20130205113721'
DECLARE @b VARCHAR(30)SELECT SUBSTRING(@a,1,4) AS '年',SUBSTRING(@a,5,2) AS '月',SUBSTRING(@a,7,2) AS '日',SUBSTRING(@a,9,2) AS '时',SUBSTRING(@a,11,2) AS '分',SUBSTRING(@a,13,2) AS '秒'
SELECT SUBSTRING(@a,1,4)+'-'+ SUBSTRING(@a,5,2)+'-'+SUBSTRING(@a,7,2) +' '+SUBSTRING(@a,9,2) +':'+SUBSTRING(@a,11,2)+':'+SUBSTRING(@a,13,2)
SET  @b= SUBSTRING(@a,1,4)+'-'+ SUBSTRING(@a,5,2)+'-'+SUBSTRING(@a,7,2) +' '+SUBSTRING(@a,9,2) +':'+SUBSTRING(@a,11,2)+':'+SUBSTRING(@a,13,2)
SELECT CAST(@b AS DATETIME)/*
年        月    日    时    分    秒
-------- ---- ---- ---- ---- ----
2013     02   05   11   37   21(1 行受影响)
---------------------------------
2013-02-05 11:37:21(1 行受影响)
-----------------------
2013-02-05 11:37:21.000(1 行受影响)*/

Answer 9

补充一下，用字符串的话，你要做到严格控制格式，比如统一为YYYYMMDDHHMMSS这样的格式，不能说一个数据是20130205113721，而另外一个数据是201325113721（也就是月日部分如果为一位就没有前面那个0），这样的话，你基本上无法获取正确的日期，从而导致数据转换失败。
另外，还是我在6楼说的那个，没必要用nvarchar，因为这个存储空间比varchar多一倍。而纯粹的数字不需要Unicode编码。不会出现文字方面的乱码情况。所以建议使用varchar，如果你的日期规则控制得当，甚至使用char（14）更加高效。

Answer 10

  create  table Car
  (
    CookiesId varchar(500)
  )
  
insert into  Car values('20130205113721')
insert into  Car values('20130208102212')
insert into  Car values('20130211095407')
insert into  Car values('20130212225649')
insert into  Car values('20130214210708')
insert into  Car values('20130215133752')
insert into  Car values('20130216093752')
insert into  Car values('20130216083752')
insert into  Car values('20130216103752')
insert into  Car values('20130216103752123')
insert into  Car values('20130220113721')
insert into  Car values('20130221113721')
insert into  Car values('20130220083721')
insert into  Car values('2013021610375212311111') --通过isdate可以检查你的字段值是否符合时间字段要求
 select *  from Car where isdate(CookiesId)=1 --1表示符合 --检查后没有符合的所以进行拼接让值符合要求
select  substring(CookiesId,1,4)+'-'+substring(CookiesId,5,2)+'-'+substring(CookiesId,7,2)+ ' '+substring(CookiesId,9,2)+':'+
 substring(CookiesId,11,2)+':'+substring(CookiesId,13,2)+'.'+case when len(substring(CookiesId,15,20))=0 then '00'else substring(CookiesId,15,20) end as CookiesId1
  into #aa  from  Car 
  --  drop table #aa
  select * from #aa where datediff(hh,CookiesId1,GETDATE()) between 0 and 8 --和当前时间相比差值在0到8之间的