SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 conversion nvchar 255 to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 04/01/2013 :  14:51:19  Show Profile  Reply with Quote
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



Edited by - Vack on 04/01/2013 14:52:31

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 04/01/2013 :  15:21:32  Show Profile  Reply with Quote
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;

Edited by - James K on 04/01/2013 15:21:46
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  15:31:58  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/01/2013 :  15:33:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000