Solved

Inserting data with datetime in sql 2014

Posted on 2016-07-20
13
75 Views
Last Modified: 2016-08-02
i try to insert data from source table where the datetime values here has nvarchar in the column datatype to a destination column with datetime as datatype in the column, it gave me below error messages.


Conversion failed when converting date and/or time from character string.
0
Comment
Question by:motioneye
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 41720971
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().
1
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41720978
1
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41721143
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'
0
 

Author Comment

by:motioneye
ID: 41722144
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.
0
 

Author Comment

by:motioneye
ID: 41722155
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41722211
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 41723130
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

Open 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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41723139
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

Open in new window

1
 
LVL 32

Expert Comment

by:ste5an
ID: 41723181
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);

Open in new window

1
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41723225
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.

Open 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.

Open 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

Open in new window

0
 

Author Comment

by:motioneye
ID: 41730681
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
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41730959
? btw, as you're using SQL Server 2014, use FORMAT().

Capture.PNG
1
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41731184
The date has a format ONLY when it is shown. Internally, where it is stored be it column or variable, THERE IS NO format! No matter how you try to change the format inside the column itself it will never happen!

A datetime value is stored internally as a pair of 2 integer numbers, one for the date part and one for the time part. You can't see that format because it makes no sense as it is so it has to be converted into a readable date format. There is a defaul date format at select/print and that is what you will see unless you use CONVERT function. As you can see bellow, no matter what format I use to set the variable the select will return always the default format:
declare
    @datetime_value as datetime

set 
    @datetime_value=getdate()
select 
    @datetime_value as set1


set 
    @datetime_value='2016-07-26 09:15'
select 
    @datetime_value as set2


set 
    @datetime_value='28-Jul-2016 09:15'
select 
    @datetime_value as set3


set 
    @datetime_value='07/28/2016 09:15'
select 
    @datetime_value as set4



-- you have to use CONVERT in order to SEE a different format:
select 
    convert(varchar(35), @datetime_value, 106) not_defualt_format

set1
-----------------------
2016-07-27 09:17:23.730

(1 row(s) affected)

set2
-----------------------
2016-07-26 09:15:00.000

(1 row(s) affected)

set3
-----------------------
2016-07-28 09:15:00.000

(1 row(s) affected)

set4
-----------------------
2016-07-28 09:15:00.000

(1 row(s) affected)

not_defualt_format
-----------------------------------
28 Jul 2016

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now