MSSQL Convert Char To Date

I have a table where the DateTime is stored as Char(14)
Example:  11272006124606    mmddyyyyhhmmss
Question:
How can I convert  11272006124606 to 2006-11-27
LVL 2
Phil ChapmanAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
You can also use (in 2012+):
declare @dt as char(14) = '11272006124606'
select convert(date, stuff( stuff(left(@dt,8),5, 0, '/') , 3, 0, '/'), 101)

Open in new window

0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try..

--

DECLARE @ AS CHAR(14) = '11272006124606'

SELECT CAST(RIGHT(SUBSTRING(@,1,8),4) + '-' + LEFT(SUBSTRING(@,1,8),2) 
+ '-' + SUBSTRING(SUBSTRING(@,1,8),3,2) AS DATE)

--

Open in new window


O/p

2006-11-27
0
 
Pawan KumarDatabase ExpertCommented:
For SQL 2012..

DECLARE @ AS CHAR(14) = '11272006124606'

SELECT CAST( CONCAT(  RIGHT(SUBSTRING(@,1,8),4) , '-' , LEFT(SUBSTRING(@,1,8),2) 
, '-' , SUBSTRING(SUBSTRING(@,1,8),3,2)) AS DATE)

Open in new window



O/p

2006-11-27
0
 
Phil ChapmanAuthor Commented:
Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.