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 |
|
Muj9
Yak Posting Veteran
75 Posts |
 Posted - 2013-10-16 : 10:56:11
|
| When converting a varchar value i get an error but cant really seen anything wrong in the data, but i have just been skimming it because the data set is huge.does any body have any idea what can be done to resolve this issue?cast( right(ltrim([Request date]),4)+substring(ltrim([Request date]),4,2)+ left(ltrim([Request date]),2)as datetime) as [Request date]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.Thank you in advance |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-16 : 11:01:12
|
quote:
Originally posted by Muj9
When converting a varchar value i get an error but cant really seen anything wrong in the data, but i have just been skimming it because the data set is huge.does any body have any idea what can be done to resolve this issue?cast( right(ltrim([Request date]),4)+substring(ltrim([Request date]),4,2)+ left(ltrim([Request date]),2)as datetime) as [Request date]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.Thank you in advance
That depends on what is in the Request date column and what the format of the string you constructed is. If you are constructing YYYYMMDD format strings (which it seems like you are), that would be independent of the date format setting. Run this query to see what you get. Those are the rows that are causing the problem:SELECT * FROM YourTableWHEREISDATE( right(ltrim([Request date]),4)+substring(ltrim([Request date]),4,2)+ left(ltrim([Request date]),2) ) = 0 |
 |
|
|
Muj9
Yak Posting Veteran
75 Posts |
Posted - 2013-10-17 : 11:37:04
|
| thank you for your help. i checked the data by eleminating year by year using substring and cte and found many years of the varchar filed like so 0009 and 1009 so there where typing errors. once i sorted the few which were causing the fields to not convert. it then just worked. i will keep your suggestion for my future silmilar problems.Thanks again. |
|
|
|
|
|
|
|
|
error when converting varchar in to datetime