SQL Server Performance Forum – Threads Archive
Converting varchar to DateTime
Hi there… I am trying to import data from Ms Access to SQL Server 2000. But in dates field it keep giving me errorInsert error, column 8(‘StartTime’, DBTYPE_DBTIMESTAMP), status 6: Data
overflow ,Invalid character value for cast specification " Please advice.. Thanks
Danny
select the data using order by clause and check the if there are any invalid dates or out of range dates…. In DTS you can use exception file to figure it out what is cuasing this… check this…
http://www.sqldts.com/249.aspx
MohammedU.
Moderator
SQL-Server-Performance.com
Here is how i solved my issue To convert the varchar datatype to DateTime all the values in varchar column shoud in format ‘mm/dd/yy’for example ’01/01/07’
If any of your value is not in this format then conversion will fail.
My values were in ‘mm/dd/yyyy’ format. And many of them were not perfect like ‘2/05/2007′ or ’01/5/2007’ or may be ‘1/5/2007’
so i used used following queries and got then in proper format to convert to DateTime data Type. –For values where month is not in MM format (5/02/2007) or (5/2/2007) update test09
set dtmvar2 = ‘0’ + substring(dtmvar2,1,len(dtmvar2))– + ‘0’ + substring(dtmvar,4,datalength(dtmvar2))
where charindex(‘/’,dtmvar2,1)= 2
go
–For Values where Day is not in proper format (05/2/2007) update test09
set dtmvar2 = substring(dtmvar2,1,3) + ‘0’ + substring(dtmvar2,4,datalength(dtmvar2))
where charindex(‘/’,dtmvar2,4)= 5
go
— for getting YYYY to YY format update test09
set dtmvar2 =substring(dtmvar2,1,6) + substring(dtmvar2,9,10)
noW your records are in perfect format to be converted to DateTime Data Type use Select Convert(dateTime,dtmvar2,1)
from test
go
Thanks
Danny
]]>