SQL Server Performance

Converting varchar to DateTime

Discussion in 'SQL Server DTS-Related Questions' started by danny123, Mar 5, 2007.

  1. danny123 New Member

    Hi there...

    I am trying to import data from Ms Access to SQL Server 2000. But in dates field it keep giving me error
    Insert error, column 8('StartTime', DBTYPE_DBTIMESTAMP), status 6: Data
    overflow ,Invalid character value for cast specification "

    Please advice..

    Thanks
    Danny
  2. MohammedU New Member

    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
  3. danny123 New Member

    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

Share This Page