How to convert string with format ddmmyyyy to date dd/mm/yyyy or dd-mm-yyyy
DECLARE @str VARCHAR(32) = '18022015';
SELECT CONVERT(DATE, STUFF(STUFF(@str,5,0,'/'),3,0,'/'),103);
The query above converts the string to something that is of data type DATE. I would recommend leaving it at that. Unless there is a compelling reason, don't do any further conversions back into a string. If you do want to convert, you can use the CONVERT functions described on this page.
Also it is better to do formatting with something besides T-SQL. As James said, leave a date as a date, it is easier to work with.
Another option:
select cast(right(@str,4) + substring(@str,3,2) + left(@str,2) as date)Like the others I always prefer to have the APP do the formatting whenever possible - then the APP has a date in native-format that it can sort/manipulate as-a-date, and not as-a-string.
In SQL 2012 the format() function was added, which might be helpful for date formatting.
No particular need to convert it, that may cause implicit conversions during output that you don't want.
SELECT STUFF(STUFF(column_name, 3, 0, '/'), 6, 0, '/')