CODEWITHSUNDEEP
sql-server-2012
vinnyvedi
vinnyvedi

Reputation: 5

Converting varchar datatype to datetime datatype using SQL 2012 management studio

I have a column which has ddmmmyyyy:hh:mm:ss.nnnnnn it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using 

update tablename
set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' + 
SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));

and then 

alter table tablename    
alter columnname datetime;

but later it shows up the error

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

How do I change it any other opinion or any modification for the above query. Please help. Thank you.

Upvotes: 1

Views: 404

Answers (3)

sallushan
sallushan

Reputation: 1147

As per your given string format, you should use datetime2 data type

Your string format is almost correct, only 1 colon is extra after Year.

If you fix that thing, you can directly cast the varchar field into datetime2. For example first you can replace the extra colon with space by running following query,

UPDATE myTable
    SET targetColumn = STUFF ( targetColumn , 10, 1, ' ')
-- ddmmmyyyy:hh:mm:ss.nnnnnn
--           \
--           this colon is extra which is at 10th position

After this you can directly ALTER your table and change the data type to datetime2.

Important: data in all the lines must contain valid date

Here is a test which shows how you can convert

CREATE TABLE testTable(testCol varchar(25));

INSERT INTO testTable(testCol)
    VALUES('03Jan2014 18:33:39.999999');

ALTER TABLE testTable ALTER COLUMN testCol datetime2;

SELECT  *
FROM    testTable

DROP TABLE testTable;

Upvotes: 1

T I
T I

Reputation: 9943

Add a new column

alter table t
add n datetime

Update the new column

update t
set n = datetimefromparts(
            cast(substring(o,6,4) as int),
            case substring(o,3,3)
                 when 'jan' then 1
                 ...
                 when 'dec' then 12
            end,
            cast(substring(o,1,2) as int),
            cast(substring(o,11,2) as int),
            cast(substring(o,14,2) as int),
            cast(substring(o,17,2) as int),
            cast(substring(o,20,6) as int)
        )

If you need to drop the old column

alter table t
drop column o

Upvotes: 0

Mikel Pascual
Mikel Pascual

Reputation: 2211

It has already been answered here: Is there a way to convert a varchar to DATETIME in SQL SERVER 2008?

He uses: convert(datetime,'24/05/2012 09:56:06',103)

Although you might have to do some substrings to adapt to a format covered by convert: http://www.sql-server-helper.com/tips/date-formats.aspx

Upvotes: 0

Related Questions