Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Inserting data with datetime in sql 2014

Posted on 2016-07-20
13
Medium Priority
?
193 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 36

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 27

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 27

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 27

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 36

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 27

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 36

Expert Comment

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

Capture.PNG
1
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 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