Take the 2-minute tour ×

I have a column which has date-values stored in varchar (format ddmmyy) like this


How to convert these values into datetime data type?

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted
Set DateFormat DMY
Select Cast(Stuff(Stuff(SomeValue, 3, 0, '-'), 6, 0, '-') As datetime)
From MyData
share|improve this answer
Using this solution, should I always set dateformat into DMY before execution? –  jrara May 20 '11 at 18:40
@jara - Yes because it ensures that Cast interprets the position of month and day correctly independent of the the default date format on the server. –  Thomas May 20 '11 at 18:42
@jara - Without DateFormat, on a US machine/server for example, it would want to interpret the date as MM-DD-YY instead of DD-MM-YY. –  Thomas May 20 '11 at 18:43
add comment

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) 
        +'-'+SUBSTRING('231280', 3, 2)+'-'+SUBSTRING('231280', 1, 2) as datetime)
share|improve this answer
add comment

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
share|improve this answer
Thanks for providing a good example! –  jrara May 20 '11 at 18:44
add comment

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)
share|improve this answer
The code built the strings in the wrong order causing an error, but the basic idea was right. I fixed the code and tested to make sure it works. –  JohnFx May 20 '11 at 18:35
@JohnFx I edited it 5 seconds after I had posted it. I named my edit, "Doh." Then, 9 minutes later, you did the same :( –  GSerg May 20 '11 at 18:41
add comment

Your Answer


