Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011

    Unanswered: Error converting varchar to datetime

    I wanted to convert a column of varchar to datetime data type. I used the following command for this:-

    USE TestDB
    SELECT TOP 5 dates,
    CONVERT(datetime, dates,101)
    FROM dbo.table_date

    I got the follwoing error:-

    Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    SELECT TOP 5 CONVERT(VARCHAR(10), Cast(table_dates.dates AS DATE), 101)
       FROM db.table_dates
       WHERE  1 = IsDate(table_dates.dates)
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011

    converting varchar to datetime worked

    Hi Pat, thanks for your suggestion, This code worked for me:-

    select * from test_date_backup
    UPDATE dt= REPLACE SELECT CONVERT(datetime, dt,101) FROM [TestDB].[dbo].[test_date]

    And I tried your code and it works perfectly as well. I wanted to change the the date values rather then only view it as varchar but then I can also do it by saving it into other table or creating a view.

    Many Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts