sql时分秒转为秒

来源：互联网发布：淘宝王者荣耀点券真假编辑：程序博客网时间：2024/05/17 03:20

declare @sql varchar(20)
set @sql='37:02:10'

--test--

SELECT substring(@sql,0,charindex(':',left(@sql,3)))
select cast(substring(@sql,0,charindex(':',left(@sql,3))) as int)*3600
+cast(substring(right(@sql,5),0,charindex(':',right(@sql,5))) as int)*60
+substring(right(@sql,5),charindex(':',right(@sql,5))+1,len(right(@sql,5))-charindex(':',right(@sql,5)))

--end test ----

declare @i int
set @i=133330

select cast(@i/3600 as varchar)
       +':'+
       case when len(@i%3600/60)=1 then '0'+cast(@i%3600/60 as varchar) else cast(@i%60/60 as varchar) end
       +':'+
       case when len(@i%60)=1 then '0'+cast(@i%60 as varchar) else cast(@i%60 as varchar) end

/*
37:02:10
*/

declare @sql varchar(20)
set @sql='37:02:10'
--SELECT substring(@sql,0,charindex(':',left(@sql,3)))
select cast(substring(@sql,0,charindex(':',left(@sql,3))) as int)*3600
+cast(substring(right(@sql,5),0,charindex(':',right(@sql,5))) as int)*60
+substring(right(@sql,5),charindex(':',right(@sql,5))+1,len(right(@sql,5))-charindex(':',right(@sql,5)))

-----------------------------------------------------------------------------------------------------------------------

由字符串转化为标准时间

Sql Server将字符串转换为日期(2006-08-15 10:36:36)转载分类：技巧程序
在Sql Server中要将字符串转为日期比如20060815104235转换为2006-08-15 10：42：35。用cast和convert都提示类型转换错误，cast和convert都需要对应的日期时间格式才能转换。抓耳挠腮着急之时，一好友给了个函数，结果OK！

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/*
Fun : 将字符串转换为时间格式
Author : YL
Create Time : 2005-7-23
*/
ALTER FUNCTION [dbo].[funStringToDateTime]
(@strTime varchar(20)) --字符串形式的时间。格式：YYYYMMDDHHMISS --> YYYY-MM-DD HH:MI:SS
RETURNS datetime
AS
Begin
Declare @strTmp as varchar(20)
Set @strTime = ltrim(rtrim(@strTime))
Set @strTmp = subString(@strTime, 1, 4)    -- "YYYY"
Set @strTmp = @strTmp + '-'     -- "YYYY-"
Set @strTmp = @strTmp + subString(@strTime, 5, 2) -- "YYYY-MM"
Set @strTmp = @strTmp + '-'     -- "YYYY-MM-"
Set @strTmp = @strTmp + subString(@strTime, 7, 2) -- "YYYY-MM-DD"
Set @strTmp = @strTmp + ' '     -- "YYYY-MM-DD "
Set @strTmp = @strTmp + subString(@strTime, 9, 2) -- "YYYY-MM-DD HH"
Set @strTmp = @strTmp + ':'     -- "YYYY-MM-DD HH:"
Set @strTmp = @strTmp + subString(@strTime, 11, 2) -- "YYYY-MM-DD HH:MI"
Set @strTmp = @strTmp + ':'     -- "YYYY-MM-DD HH:MI:"
Set @strTmp = @strTmp + subString(@strTime, 13, 2) -- "YYYY-MM-DD HH:MI:SS"

return cast(@strTmp as datetime)
End

如果是这样的一个字符串"2000年1月2日",用sql语句转换成"2000-1-2"的标准日期格式
select Replace(Replace(Replace('2000年1月2日','年','-'),'月','-'),'日','')
select Replace(Replace(Replace(字段名,'年','-'),'月','-'),'日','') from table1

/*
CONVERT(NVARCHAR(10), CAST([UPTIME] AS DATETIME),8)
*/

0 0