巧用STUFF完成字符串替换

来源：互联网发布：淘宝送的通用型贴膜器编辑：程序博客网时间：2024/06/11 07:37

相关帖子：　点击打开链接

IF OBJECT_ID('fn_split12charToDate') IS NOT NULLBEGINDROP FUNCTION dbo.fn_split12charToDateENDGO-- =============================================-- Author:yenange-- Create date: 2013-11-7-- Description:将 '201310101232' 类似的字符串转换成datetime类型-- =============================================CREATE FUNCTION dbo.fn_split12charToDate(@dateStr VARCHAR(12))RETURNS DATETIMEASBEGINDECLARE @str VARCHAR(MAX)SET @str = STUFF(STUFF(STUFF(STUFF(@dateStr, 5, 0, '-'),8,0,'-'),11,0,' '),14,0,':')RETURN CAST(@str AS DATETIME);ENDGO--测试代码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.aspSELECT DATEDIFF(n, dbo.fn_split12charToDate(@d1), dbo.fn_split12charToDate(@d2))/* 结果: 59 */