22,207
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('fn_split12charToDate') IS NOT NULL
BEGIN
DROP FUNCTION dbo.fn_split12charToDate
END
GO
-- =============================================
-- Author: yenange
-- Create date: 2013-11-7
-- Description: 将 '201310101232' 类似的字符串转换成datetime类型
-- =============================================
CREATE FUNCTION dbo.fn_split12charToDate
(
@dateStr VARCHAR(12)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @str VARCHAR(MAX)
SET @str = STUFF(STUFF(STUFF(STUFF(@dateStr, 5, 0, '-'),8,0,'-'),11,0,' '),14,0,':')
RETURN CAST(@str AS DATETIME);
END
GO
--测试代码
DECLARE @d1 VARCHAR(12),@d2 VARCHAR(12);
SELECT @d1 = '201310101232', @d2 = '201310101331';
--DATEDIFF(datepart,startdate,enddate) / 分钟 mi, n / http://www.w3school.com.cn/sql/func_datediff.asp
SELECT DATEDIFF(n, dbo.fn_split12charToDate(@d1), dbo.fn_split12charToDate(@d2))
/* 结果: 59 */
declare @t1 varchar(12)
declare @t2 varchar(12)
set @t1 = '10:20:35'
set @t2 = '10:35:35'
--@t2减去@t1
select datediff(minute,'1990-01-01 '+@t1,'1990-01-01 '+@t2)
/*
15
*/
-- 这两列 相减得到分钟数。
select [字段1],
[字段2],
datediff(mi,cast([字段1] as datetime),cast([字段2] as datetime)) '分钟数'
from [表名]
-- 把字符类型时间 装为 datetime类型的语句
select cast([字段名] as datetime)
from [表名]
declare @x1 varchar(12),@x2 varchar(12)
select @x1='201310101232',@x2='201310101331'
select datediff(mi,
cast(left(@x1,4)+'-'+substring(@x1,5,2)+'-'+substring(@x1,7,2)+' '+substring(@x1,9,2)+':'+substring(@x1,11,2)+':00' as datetime),
cast(left(@x2,4)+'-'+substring(@x2,5,2)+'-'+substring(@x2,7,2)+' '+substring(@x2,9,2)+':'+substring(@x2,11,2)+':00' as datetime)) '分钟数'
/*
分钟数
-----------
59
(1 row(s) affected)
*/
DECLARE @a VARCHAR(12)
DECLARE @b VARCHAR(12)
SET @a='201310101232'
SET @b='201310101233'
DECLARE @c VARCHAR(30)
DECLARE @d VARCHAR(30)
SELECT @c=CONVERT(VARCHAR(30),SUBSTRING(@a,1,4)+'-'+SUBSTRING(@a,5,2)+'-'+SUBSTRING(@a,7,2)+' '+SUBSTRING(@a,9,2)+':'+SUBSTRING (@a,11,2)+':'+'00.000',121)
SELECT @d=CONVERT(VARCHAR(30),SUBSTRING(@b,1,4)+'-'+SUBSTRING(@b,5,2)+'-'+SUBSTRING(@b,7,2)+' '+SUBSTRING(@b,9,2)+':'+SUBSTRING (@b,11,2)+':'+'00.000',121)
SELECT DATEDIFF(mi,@c,@d) AS '分钟差'
/*
SELECT CONVERT(VARCHAR(30),SUBSTRING(@a,1,4)+'-'+SUBSTRING(@a,5,2)+'-'+SUBSTRING(@a,7,2)+' '+SUBSTRING(@a,9,2)+':'+SUBSTRING (@a,11,2)+':'+'00.000',121)
*/