SQL Server: Date conversion problem?

I have 开发者_高级运维a column which has date-values stored in varchar (format ddmmyy) like this

231280
121280
131185
...

How to convert these values into datetime data type?

Set DateFormat DMY
GO
Select Cast(Stuff(Stuff(SomeValue, 3, 0, '-'), 6, 0, '-') As datetime)
From MyData

Use substring to get year, month, and day, if year greater than 11 add 19 to it if not add 20, to get year in format 2009, but this is your query just instead of string add your column name

 select CAST(
            CASE WHEN CAST(SUBSTRING('231280', 5, 2) AS INT) >11
                    THEN '19'+SUBSTRING('231280', 5, 2) 
                    else '20'+SUBSTRING('231280', 5, 2) 
                END 
        +'-'+SUBSTRING('231280', 3, 2)+'-'+SUBSTRING('231280', 1, 2) as datetime)

You'd have to use some substring footwork to convert your string to a known date format. Here's an example converting the string to format 3, "British/French":

declare @YourTable table (YourColumn varchar(50))
insert @YourTable
          select '231280'
union all select '121280'
union all select '131185'

select  convert(datetime, substring(YourColumn,1,2) + '/' + 
    substring(YourColumn,3,2) + '/' + substring(YourColumn,5,2), 3)
from    @YourTable

Because this format is non standard, use

DECLARE @field char(6)
SET @field = '231280'
select convert(datetime, right(@field, 2) +   substring(@field, 3, 2) + left(@field, 2)  , 12)