Jeff Moden (7/24/2013)

---

savycara (7/24/2013)

---

DECLARE @d varchar(6) = '20114'

SELECT try_convert(date, substring(@d, 1, 4) +

CASE WHEN len(@d) = 5 THEN '0' ELSE '' END +

substring(@d, 5, 2) + '01')

This worked but I am not getting in 101 style that is 04/01/2011

If you don't mind a non-Ansi solution (and I don't), this is pretty easy. That, not withstanding, it would be good to heed the warnings about formatting dates on the backend.

WITH

cteTestData(D) AS

( --=== This is test data and is not a part of the solution

SELECT '20111' UNION ALL

SELECT '20112' UNION ALL

SELECT '20113' UNION ALL

SELECT '20114' UNION ALL

SELECT '20115' UNION ALL

SELECT '20116' UNION ALL

SELECT '20117' UNION ALL

SELECT '20118' UNION ALL

SELECT '20119' UNION ALL

SELECT '201110' UNION ALL

SELECT '201111' UNION ALL

SELECT '201112' UNION ALL

SELECT '20121'

)

SELECT OriginalData = D

, Converted = CONVERT(CHAR(10),DATEADD(mm,SUBSTRING(D,5,2)-1,SUBSTRING(D,1,4)),101)

FROM cteTestData

;

Results:

OriginalData Converted

------------ ----------

20111 01/01/2011

20112 02/01/2011

20113 03/01/2011

20114 04/01/2011

20115 05/01/2011

20116 06/01/2011

20117 07/01/2011

20118 08/01/2011

20119 09/01/2011

201110 10/01/2011

201111 11/01/2011

201112 12/01/2011

20121 01/01/2012

If you remove the CONVERT, it will be an ANSI solution that returns the DATETIME datatype which is better for the front-end. I wouldn't even think of permanently storing a formatted date in anything but a staging table being made ready for output to a text file.

If it were me, I'd find the person that wrote the code that created original formatted date mess and take them to the woodshed for a bit of education.