Varchar to datetime in SQL Server 2008

Question

I'm trying to capture the datepart from an filename and trying to convert it to datetime datatype.

SELECT CONVERT(Datetime, substring('EmployeeExcel_03312013',15,22), 120)

Getting below error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Any ideas? Please suggest if I am missing anything?

Answer 1

You can use the following:

SELECT 
  convert(datetime, 
           right('EmployeeExcel_03312013', 4)
            +substring('EmployeeExcel_03312013',15,4), 112)

This get the value in the format 20130331 before the being converted to a date.

See SQL Fiddle with Demo

Answer 2

You can give this a go

DECLARE @Date char(8)
set @Date=substring('EmployeeExcel_03312013',15,22)
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

Output:

-----------------------
2013-03-31 00:00:00.000

(1 row(s) affected)

SQLFiddle Example

Answer 3

There is a similar post on here: Convert varchar into datetime in SQL Server

To make it more simple:

select convert(datetime, '03312013', 120) --produces error

This indicates that CONVERT doesn't know what to do with a string formatted that way.

If you split up the date to look more like '03-31-2013' (which is what the link above indicates) or something similar you'll be good.