目标:
如何将最后 14 位数字提取并格式化为 DATETIME
类型值?
情况:
我有一个包含几个不同字符的id
列,最后 14 个字符代表DATETIME
.
例:
abcdefghj02002216112014
钥匙:
这14位数字是:second
、minute
、hour
、day
、month
、year
查询:
SELECT RIGHT(id,15)
FROM table
你可以这样做:
declare @variable varchar(50)='abcdefghj02002216112014'
declare @T bigint=RIGHT(@variable, 14)
SELECT DATETIMEFROMPARTS ( @T % 10000,
(@T / 10000) % 100,
(@T / 1000000) % 100,
(@T / 100000000) % 100,
(@T / 10000000000) % 100,
(@T / 1000000000000) % 100, 0 ) AS Result;
结果:
2014-11-16 22:00:02
日期时间来自零件:
https://learn.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-2017
您可以将 DATETIMEFROMPARTS 与 SUBSTRING 函数一起使用
此函数返回指定日期和时间参数的日期时间值。
如下:-
create table #MyTable (id nvarchar(100))
insert #MyTable values ('abcdefghj02002216112014')
select DATETIMEFROMPARTS(
SUBSTRING(RIGHT(id,14),11,15) /*year*/,
SUBSTRING(RIGHT(id,14),9,2) /*month*/,
SUBSTRING(RIGHT(id,14),7,2) /*day*/,
SUBSTRING(RIGHT(id,14),5,2) /*hour*/,
SUBSTRING(RIGHT(id,14),3,2) /* minute*/,
SUBSTRING(RIGHT(id,14),0,3) /*seconds*/,
00 /*milliseconds*/)
from #MyTable
drop table #MyTable
长话短说
declare @x varchar(50)='abcdefghj02002216112014'
declare @y varchar(14)=RIGHT(@x, 14)
declare @dtPart varchar(10) = CONCAT(LEFT(RIGHT(@y, 8),2), '/', substring(@y, 9,2), '/', RIGHT(@y,4))
declare @timePart varchar(8)= concat(substring(@y,5,2), ':', substring(@y,3,2),':', left(@y,2))
declare @fullDate varchar(19) =concat(@dtPart, ' ', @timePart)
select Convert(datetime,@fullDate, 103)
多么可怕的格式!
也许这会有所帮助
例
Select ID
,NewValue = convert(datetime,right(S,4)+'-'+left(right(S,6),2)+'-'+left(right(S,8),2)+' '+substring(s,5,2)+':'+substring(s,3,2)+':'+substring(s,1,2))
From YourTable A
Cross Apply ( values (right(ID,14)) ) B(s)
返回
ID NewValue
abcdefghj02002216112014 2014-11-16 22:00:02.000