• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2748
  • Last Modified:

sql - nvarchar to datetime

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
amillyard
Asked:
amillyard
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Mez4343Commented:
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
 
tigin44Commented:
use this with the appropriate format parameter..

UPDATE db_table
SET myDateTime =  CONVERT(datetime, nvarchar255DateTime, 101)
0
 
amillyardAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
amillyardAuthor Commented:
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
 
tigin44Commented:
so you have values having irregular formats... check the values and correct or discard the irregular formated values...
0
 
SharathData EngineerCommented:
Run this and check the non-dates in your table.
select myDateTime from db_table where ISDATE(myDateTime) = 0

Open in new window

0
 
amillyardAuthor Commented:
Sharath_123:  out of 350000 records -- the above script returned 130000

What does this mean please?
0
 
SharathData EngineerCommented:
That means those are not proper dates. Post some of those non-dates.
0
 
amillyardAuthor Commented:
Sharath_123:

29/10/2010
15/11/2010
0
 
SharathData EngineerCommented:
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
 
amillyardAuthor Commented:
Sharath_123:  works perfectly now :-)  thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now