Showing posts with label fileds. Show all posts
Showing posts with label fileds. Show all posts

Tuesday, March 20, 2012

Convert Varchar(10) to datetime

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?

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.

|||I am moving this to the Transact-SQL forum, which can provide further insights.|||don't change the dateformat

specify the style instead

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

Convert Varchar(10) to datetime

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?

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.

|||I am moving this to the Transact-SQL forum, which can provide further insights.|||don't change the dateformat

specify the style instead

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