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.
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; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 valueseeSELECT ISDATE('2013'),ISDATE(200905)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|