Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 conversion nvchar 255 to datetime

Author  Topic 

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-04-01 : 14:51:19
I have imported data into a table from excel. I have two date fields that came in as nvarchar 255. I'm trying to get those into datetime format. I tried creating two new fields in my table that are datetime. then doing and update statement to move the values into these fields.

When I do that I get:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I get the same message when trying to import the data from excel and on the edit mappings changing the field type to datetime. I have also tried different formats in excel with no luck.

The value in the date fields look like; 2013-04-01 00:00:14


James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-01 : 15:21:32
Are you using cast or convert? Try the following query to see which rows are causing the problem:
select * from YourTable where ISDATE(YourVarcharColumn) = 0;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 15:31:58
see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

it may be that you've ambiguos formatted date values present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 15:33:58
quote:
Originally posted by James K

Are you using cast or convert? Try the following query to see which rows are causing the problem:
select * from YourTable where ISDATE(YourVarcharColumn) = 0;



Word of caution
ISDATE can give 1 to incomplete date values as well as it tries and successfully converts them to a date value

see

SELECT ISDATE('2013'),ISDATE(200905)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -