[sql] Convert YYYYMMDD to DATE

I have a bunch of dates in varchar like this:

20080107
20090101
20100405
...

How do I convert them to a date format like this:

2008-01-07
2009-01-01
2010-04-05

I've tried using this:

SELECT [FIRST_NAME]
      ,[MIDDLE_NAME]
      ,[LAST_NAME]      
      ,cast([GRADUATION_DATE] as date)      
  FROM mydb

But get this message:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

I was also facing the same issue where I was receiving the Transaction_Date as YYYYMMDD in bigint format. So I converted it into Datetime format using below query and saved it in new column with datetime format. I hope this will help you as well.

SELECT
convert( Datetime, STUFF(STUFF(Transaction_Date, 5, 0, '-'), 8, 0, '-'), 120) As [Transaction_Date_New]
FROM mydb

Use SELECT CONVERT(date, '20140327')

In your case,

SELECT [FIRST_NAME],
       [MIDDLE_NAME],
       [LAST_NAME],
       CONVERT(date, [GRADUATION_DATE])     
FROM mydb

In your case it should be:

Select convert(datetime,convert(varchar(10),GRADUATION_DATE,120)) as
'GRADUATION_DATE' from mydb