Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

In SQL Server, I have a varchar column with value such as:

 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22

QUESTIONS

  • How do I convert this column to a datetime column with real datetimes?
  • If this is not possible, how do I just create a new column and copy this values into it but as datetime values?

Many thanks

share|improve this question
1  
Can you show some non-identical rows? e.g. what does September 30th look like? – Aaron Bertrand♦ May 15 '12 at 19:23
2  
This is a good example as to why anyone working with a database should use ISO date/time formats. Though at least who/whatever created this file provided an unambiguous date format. – Jon of All Trades May 15 '12 at 19:48

5 Answers

up vote 6 down vote accepted

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME, 
    REPLACE(LEFT(varchar_col, 11), '/', ' ') 
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.

share|improve this answer
up vote 1 down vote

1-. you can check this post: http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server

someone has the same problem and could solved it with this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)

2-. if you want to add the same value to a new column , first create the column with an 

ALTER TABLE MyTable ALTER COLUMN NullCOl DATATYPE NOT NULL;

and after that you can make

UPDATE TABLE MyTable SET NewCol=CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2)) //replace the last part for the code that you use to convert from varchar to date.

Regards

share|improve this answer
up vote 1 down vote

Try the following:

select convert(datetime,(left('07/May/2012:00:06:22',11) + ' ' + 
        right('07/May/2012:00:06:22',8)))

Against a whole table, it would look like:

select convert(datetime,(left(YourColumn,11) + ' '
      + right(YourColumn,8)))

Or:

select convert(datetime,stuff('07/May/2012:00:06:22',12,1,' '))

select convert(datetime,stuff(YourColumn,12,1,' '))
share|improve this answer
up vote 3 down vote

If you can reformat your CSV to 07/May/2012 00:00:00, it will work. SQL Server doesn't like the colon between date and time.

Otherwise, I agree with @HLGEM - you need a staging table.

share|improve this answer
1  
Please review this merge ;-) – jcolebrand♦ May 15 '12 at 22:36
up vote 4 down vote

Likely you have some data that doesn't fit the datetime column somehwere in the file. Insert to a staging table with a varchar or navachar filed for the column and look at the data. You may need to adjust some of it (or null out bad records) before loading to the real table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.