Solved

sql - nvarchar to datetime

Posted on 2011-03-14
11
2,470 Views
Last Modified: 2012-05-11
I am trying to cast from nvarchar[255] to datetime as follows:

UPDATE db_table
SET myDateTime = cast([nvarchar255DateTime] as datetime)

Getting the following SQL scripting error:

Msg 242, Level 16, State 3, Line 3
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.


sample data for  nvarchar[255]DateTime:    01/10/2010
0
Comment
Question by:amillyard
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 10

Expert Comment

by:Mez4343
ID: 35133455
I would use DatePart function and break out each part to set a DateTime column.

UPDATE db_table
SET myDateTime = CAST(DATEPART(yyyy, nvarchar255DateTime) AS varchar) + '-' +
                     CAST(DATEPART(mm, nvarchar255DateTime) AS varchar) + '-' +
       CAST(DATEPART(dd, nvarchar255DateTime) AS varchar)

0
 
LVL 26

Expert Comment

by:tigin44
ID: 35133457
use this with the appropriate format parameter..

UPDATE db_table
SET myDateTime =  CONVERT(datetime, nvarchar255DateTime, 101)
0
 

Author Comment

by:amillyard
ID: 35133500
Mez4343:  thank you for the feedback - at the moment I am getting the following sql scripting error:

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

Author Comment

by:amillyard
ID: 35133507
tigin44:  getting the following sql scripting error:

Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 35133575
so you have values having irregular formats... check the values and correct or discard the irregular formated values...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

Expert Comment

by:Sharath
ID: 35133788
Run this and check the non-dates in your table.
select myDateTime from db_table where ISDATE(myDateTime) = 0

Open in new window

0
 

Author Comment

by:amillyard
ID: 35133853
Sharath_123:  out of 350000 records -- the above script returned 130000

What does this mean please?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35133876
That means those are not proper dates. Post some of those non-dates.
0
 

Author Comment

by:amillyard
ID: 35133887
Sharath_123:

29/10/2010
15/11/2010
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35134067
I don't understand what you want to update here. If the underlying data type of myDateTime is varchar, you cannot change the type with an UPDATE statemen. Instead, you can alter the column data type.
But before altering it to datetime, you can change the data to proper date format. Can you try this UPDATE statement?
UPDATE db_table 
   SET myDateTime = CASE 
                      WHEN ISDATE(date_col) = 0 THEN CONVERT(DATETIME,SUBSTRING(myDateTime,7,4) + SUBSTRING(myDateTime,4,2) + LEFT(myDateTime,2))
                      ELSE CONVERT(DATETIME,myDateTime) 
                    END

Open in new window

0
 

Author Closing Comment

by:amillyard
ID: 35134097
Sharath_123:  works perfectly now :-)  thank you.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

10 Experts available now in Live!

Get 1:1 Help Now