0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3052850
  • 178
  • 3
  • 17

3 Answers3

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

You can use cast function

Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20
  • Thank You, this worked. I also tried SELECT CONVERT(datetime2, StartDATE,12) which works fine. However for some reason when I process my EndDATE I receive the error Conversion failed when converting date and/or time from character string. – user3052850 Sep 07 '16 at 10:53
0

you need to convert to char first because converting to int adds those days to 1900-01-01

select CONVERT (datetime,convert(char(8),rnwl_efctv_dt ))

here are some examples

select CONVERT (datetime,5)
1900-01-06 00:00:00.000

select CONVERT (datetime,20100101)

blows up, because you can't add 20100101 days to 1900-01-01..you go above the limit

convert to char first

declare @i int
select @i = 20100101
select CONVERT (datetime,convert(char(8),@i))
Tamilmani Purushothaman
  • 59
  • 1
  • 12
-1
SELECT convert(varchar, StartDATE , 113) from ur table
KyLim
  • 468
  • 1
  • 6
  • 22