CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
jrara
jrara

Reputation: 16981

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?

Upvotes: 0

Views: 248

Answers (4)

Thomas
Thomas

Reputation: 64635

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

Upvotes: 1

GSerg
GSerg

Reputation: 78134

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)

Upvotes: 1

Andomar
Andomar

Reputation: 238048

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

Upvotes: 1

Senad Meškin
Senad Meškin

Reputation: 13756

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)

Upvotes: 1

Related Questions