Inserting data with datetime in sql 2014

Well, your text column does no contain a valid date/time value.

As it's SQL Server 2014, use TRY_PARSE() and/or TRY_CAST().

please read up this article
https://www.experts-exchange.com/articles/1499/DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html

You will have to identify what rows contain a wrong data format. For this you will have to import the file into a working table where the date column would be of varchar or nvarchar type, size 35 to make sure. After the import you will run a query like this:

select * from YourWorkingTable where isdate(date_column)<>1

You will fix those one by one and then from this table you will import data into your real table. A universal date format that will be always accepted by SQL l server is

YYYY-MM-DD HH:MI:SS.mmm

where HH is 01-24

So when you fix those dates makee surre you use is format. For example for right now datetime you would use:

'2016-07-20 10:03:00.000'

for evening same hour will be

'2016-07-20 22:03:00.000'

Hi Zberteoc
I tried with your suggestion by running select * from YourWorkingTable where isdate(date_column)<>1 this query somehow return all  rows from the table, the table here which contain  value as date like this 2015-12-11 13:23:21.0000000 and currently with nvarchar as data types for the column.

Guys,
Here is what I'm working now

select statdate, replace(convert(VARCHAR(10),statdate, 106), ' ', '-') as newrequestdate from [Show_Test]


status_dates_prod_date             newrequestdate
2014-06-11 13:10:39.0000000      2014-06-11
2014-11-17 08:52:48.0000000      2014-11-17
2012-06-11 16:45:04.0000000      2012-06-11


But now, what I want is the newrequestdate has dateformat like this 04-Dec-2013 instead of above.

No
Datetime value, as such, has no specific format (short answer)
Only the display of a date, being a text, will use some format. If you don't supply a fomat, an implicit format will be used
Read ip my article above

Here is a test i ran with the value you provided:

declare
    @dte nvarchar(35)='2015-12-11 13:23:21.0000000'

select 
    isdate(@dte) as DateCheck

-- Result:
DateCheck
-----------
0

Select allOpen in new window

As you can see there is nothing wrong with that format. It is possible however that you have some non printable characters inside that column, which will render the value as a no date. You will have to copy the value from that column that will have as result for isdate(date_column) 0 and paste it into a text editor that will be able to show you hidden characters. Notepad++ is free and good for that. In that case you will have to clean up the column by removing any bad characters.

For the other issue with the format here is what you should do:

declare
    @dte datetime2='2015-12-11 13:23:21.0000000'

select 
    replace(convert(varchar(15), @dte, 106),' ','-') as newrequestdate

-- Result:
newrequestdate
--------------
11-Dec-2015

Select allOpen in new window

As Guy already pointed out by his article link: Depending on the data type, it is not convertible, due to the precision not supported by that data type (DATETIME does not support this precision of milliseconds):

DECLARE @dte NVARCHAR(35)= '2015-12-11 13:23:21.0000000';

SELECT  ISDATE(@dte) AS DateCheck ,
        TRY_CAST(@dte AS DATE) ,
        TRY_CAST(@dte AS DATETIME) ,
        TRY_CAST(@dte AS DATETIME2) ,
        TRY_CAST(@dte AS TIME);

Select allOpen in new window

Oh, wow, I was blinded and did not see that the result was 0 in my post. I expected IsDate to work for datetime2 (precision 7 digits millisec) type as well. I apologize for my mistake.

@motioneye

In that case the mistery is solved. The error in your question is due to datetime of the target column. You cannot assign a datetime2 string date value implicitly to a datetime column, you have to cast it from nvarchar to datetime2 first:

This fails:

declare
    @dte datetime
SET @dte=N'2015-12-11 13:23:21.0000000'

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Select allOpen in new window

This will work:

declare
    @dte datetime
SET @dte=cast(N'2015-12-11 13:23:21.0000000' as datetime2)

Command(s) completed successfully.

Select allOpen in new window

So this is what you will have to do from your source table to be able to insert into the target table datetime column:

select cast(nvarchar_date_column as datetime2) from YourSourceTable

Select allOpen in new window

Okay,
I'm now able to insert to the datetime2 column, but now I can't  update the date to the format I want. For example, when I ran a query below, it refuse to convert the statdate to format like 18-aug-2009

select statdate, replace(convert(VARCHAR(10),statdate, 106), ' ', '-') as newrequestdate from [Show_Test]

statdate      statdate
2009-08-18 04:06:41.0000000      2009-08-18
2011-08-10 16:37:59.0000000      2011-08-10
2011-01-20 11:36:50.0000000      2011-01-20

? btw, as you're using SQL Server 2014, use FORMAT().