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?

Answers


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)

Need Your Help

How to use part of a filename as a variable in C++

c++

I have a few thousand pcap files that I'm trying to parse as part of a research project. They are all named as xxx.xxx.xxx.xxx_yyy.yyy.yyy.yyy.pcap where the first IP address is the one I'm trying...

Serialization problem

c# serialization

The situation is like this : Main project A. and a class library B. A references B

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.