CODEWITHSUNDEEP
sqlsql-serverdatecastingsubstring
Nisal Malinda Livera
Nisal Malinda Livera

Reputation: 401

How to cast a character set into a date format in MS SQL Server

When I try to cast a character set to the date format i get an error saying, Conversion failed when converting date and/or time from character string I used the following query,

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

On the following records,

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

What am i doing wrong here? Is my date format correct?

Upvotes: 1

Views: 158

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

One option... 

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),'_','-'))

Returns

2017-04-02

For the table

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

Returns

sfd_devtracker_back_2017_04_04_094339_4242105.bak

EDIT Actually, no need to convert to date

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

Upvotes: 3

BJones
BJones

Reputation: 2460

You were a little off on the numbers. Here's another option:

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

Upvotes: 0

Related Questions