Search code examples
sql-servernvarchardatetime2

SQL Server: convert StartDATE 20140804 Nvarchar to Datetime2

I'm using SQL Server 2008 and I did an import from a flat file. I couldn't import the datetime column properly so I specified it temporarily as a nvarchar(50).

Now I want to convert it to datetime2 format. However when doing so, I get the error

Conversion failed when converting date and/or time from character string.

The data that is currently in my nvarchar(50) column looks like this:

20140804

And I need to convert it to:

2014-08-04 00:00:00.0000000.

Note that I do not only want to convert one date, but all StartDATE values in my table and insert them to another table

Any help is appreciated.

Solution 
  • Insert into targettab(datecol,<othercols....>)
select cast(datefield as datetime2),<othercols...> from sourcetab

    You can use cast function