none
Convert Varchar(10) to datetime

    Question

  • Dear Friends,

    I'm having some problems to convert a varchar(10) to datetime. The problem is: 

    I have on table with some fileds, and I will talk only about 2. I have the field 1 that is a datetime with the format  2004-02-16 00:00:00.000 and filed 2 that is a varchar(10) with the format 16/02/2004.

    I need to convert the field 2 to datetime to use the condition Field1>Field2

    At the moment I have this:

    SELECT RKFolder, RKData, CONVERT(DATETIME, CONVERT(VARCHAR(10), Maturity, 121), 121) AS Campo2

    FROM FL_BondsSynthetic

     

    And the error is:

    Msg 242, Level 16, State 3, Line 1

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

     

    Could you help me?

    Wednesday, March 14, 2007 11:27 AM

Answers

  • Hopefully, this will give you the clue you seek:

    DECLARE @MyDate varchar(10)
    SET @MyDate = '16/02/2004'

    SET dateformat dmy
    SELECT convert( datetime, @MyDate )

    If you get an 'Out of Range' error, there is some non-conforming data that will have to be found and corrected.

    Wednesday, March 14, 2007 3:13 PM
    Moderator
  • don't change the dateformat

    specify the style instead

    DECLARE @MyDate varchar(10)
    SET @MyDate = '16/02/2004'
    SELECT convert( datetime, @MyDate, 103)

    Thursday, March 15, 2007 5:41 AM

All replies

  • Hopefully, this will give you the clue you seek:

    DECLARE @MyDate varchar(10)
    SET @MyDate = '16/02/2004'

    SET dateformat dmy
    SELECT convert( datetime, @MyDate )

    If you get an 'Out of Range' error, there is some non-conforming data that will have to be found and corrected.

    Wednesday, March 14, 2007 3:13 PM
    Moderator
  • I am moving this to the Transact-SQL forum, which can provide further insights.
    Wednesday, March 14, 2007 6:55 PM
  • don't change the dateformat

    specify the style instead

    DECLARE @MyDate varchar(10)
    SET @MyDate = '16/02/2004'
    SELECT convert( datetime, @MyDate, 103)

    Thursday, March 15, 2007 5:41 AM