Solved

Inserting data with datetime in sql 2014

Posted on 2016-07-20
13
100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 33

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 143

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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 143

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
 
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 33

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 33

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 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