Convert string to date Format string in sql server

Try STUFF:

SELECT STUFF(STUFF(your_string_column, 3, 0, '/'), 6, 0, '/')
FROM ...

SELECT Convert(varchar(10), CAST(MyColumn AS datetime), 103)

This extracts the date from a string that is followed by a space. This took me awhile to figure out so I thought I'd share:

SELECT reference, Convert(date,Left(history,charindex(
    ' ',Left(history,12)) - 1)) as theDate
FROM productinfo
Order BY theDate DESC

It's an old question, but I ended up here in Feb 2018, so...

In SQL Server 2012+ consider Format
- ref https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql (B and D)

For 2008 and above:

declare @d char(8)
 -- string manipulation assumes clean data
set @d = '31012010'

SELECT LEFT(@d, 2) +'/'+ SUBSTRING(@d, 3, 2) +'/'+ RIGHT(@d, 4)  As [with slashes]

String manipulation can be inefficient - make sure you understand the impact.
E.g. you would never put this code in a join clause (may be okay on a reporting server).

Personal opinion: the answer from Mark Byers STUFF(STUFF(@d, 3, 0, '/'), 6, 0, '/')) certainly works, but I think it is less maintainable if you have non-DBAs working in your code. (STUFF ref https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql)