Dfyjkt

My Filename column looks like 'D181115.T000000'. I used the following code to make it a string, looks like '2018-11-15'.

select '20' + substring(filename, 2,2) + '-' + substring(filename, 4,2) + '-' + substring(filename,6,2)
from table_name

Then I want to convert the string to date type (because I need to sort by date)

select convert(datetime, '20 + substring(filename, 2,2) + '-' + substring(filename, 4,2) + '-' + substring(filename,6,2)')
from table_name

Then I got this error message:

The data types varchar and varchar are incompatible in the subtract
operator.

Any help will be greatly appreciated!

I suspet the database is SQL Server. In that case one can use just

select cast('20' + substring('D181115.T000000', 2,6) as date) 

or

select try_cast('20' + substring('D181115.T000000', 2,6) as date) 

YYYYMMDD is one of the two unambiguous date formats. The other is the full ISO8601 date+time format. YYYY-MM-DD on the other hand depends on the DATEFORMAT setting

Update

I'd suggest performing this conversion as part of data loading though. Applying functions to a field prevents the server from using any indexes that cover the field. The server will have to scan the entire table in order to produce the final values used for filtering and sorting.

At least consider addd an indexed computed column that produces the file date

I want to convert the string to date type

Look at the first and 2nd statements you included, they are different in that you are missing a quote and you added an extra quote in the second one.

declare @filename varchar(20) = 'D181115.T000000'
select convert(datetime, '20' + substring(@filename, 2,2) + '-' + substring(@filename, 4,2) + '-' + substring(@filename,6,2))

Produces output:

2018-11-15 00:00:00.000