• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2647
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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