如何在 MS SQL Server 中将字符集转换为日期格式

Question

当我尝试将字符集转换为日期格式时，出现错误，说Conversion failed when converting date and/or time from character string我使用了以下查询，

SELECT TOP 1 FileName
    FROM #myFiles order by
    cast(
        (SUBSTRING(filename,23,4))+'-'+
        (SUBSTRING(filename,28,2))+'-'+
        (SUBSTRING(filename,30,2)) as date)
    desc

在以下记录中，

sfd_devtracker_back_2017_04_02_094339_4242105.bak
sfd_devtracker_back_2017_04_03_094339_4242105.bak
sfd_devtracker_back_2017_04_04_094339_4242105.bak

我在这里做错了什么？我的日期格式是否正确？

Answer 1

一个选项...

Declare @S varchar(max)='sfd_devtracker_back_2017_04_02_094339_4242105.bak'
Select try_convert(date,replace(substring(@S,charindex('_20',@S)+1,10),'_','-'))

返回

2017-04-02

对于表

Declare @YourTable table (FileName varchar(max))
Insert Into @YourTable values
('sfd_devtracker_back_2017_04_02_094339_4242105.bak'),
('sfd_devtracker_back_2017_04_03_094339_4242105.bak'),
('sfd_devtracker_back_2017_04_04_094339_4242105.bak')
Select Top 1 *
 From  @YourTable
 Order By try_convert(date,replace(substring(FileName,charindex('_20',FileName)+1,10),'_','-')) Desc

返回

sfd_devtracker_back_2017_04_04_094339_4242105.bak

编辑实际上，无需转换为日期

Order By substring(FileName,charindex('_20',FileName)+1,10) Desc

Answer 2

你在数字上有点不对劲。这是另一个选项：

SELECT CAST(
    (SUBSTRING('sfd_devtracker_back_2017_04_04_094339_4242105.bak',21,4))+'-'+
    (SUBSTRING('sfd_devtracker_back_2017_04_04_094339_4242105.bak',26,2))+'-'+
    (SUBSTRING('sfd_devtracker_back_2017_04_04_094339_4242105.bak',29,2)) AS date) DT
    ,FileName
ORDER BY DT DESC