Convert varchar to datetime!

Hi:

I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime.

I use Query like:

SELECT

CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +
CAST(SUBSTRING(date_sent,4,2) as int) + '/'+CAST(SUBSTRING(DATE_SENT,7,4) as int)

From MyTable

It is not working, anybody can give me some advise!

thanks!

DECLARE @.Date_Sent CHAR(10)
SET @.Date_Sent = '28/02/2004'
SELECT CONVERT(DATETIME, @.Date_Sent, 103)|||

Try:

SELECT

Cast(SUBSTRING(Date_Sent,4,2) + '/' +
SUBSTRING(date_sent,1,2) + '/'+SUBSTRING(DATE_SENT,7,4) as datetime)

|||

The solution fromPDraigh worked for me .

Thanks

really should have designed database to use datetime in first place , but this is a nice work around

|||

Strange !! it gave me an error in the SQL statement !!

my field is a varchar