convert string to yyyy/mm/dd using sql 2000

Hi,

I'm trying to insert a string in SQL that look like this '24/01/2010' and I'm get an error that I cannot convert string to datetime.  I'm trying to convert '24/01/2010' to '2010/01/24' (yyyy/mm/dd) and I need help as to how to go about it. 

Your assistance would be greatly appreciated.

ItuKosh

CONVERT(datetime,'24/01/2010',103)

u can use convert or cast function in sql server for convert datatype

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

http://msdn.microsoft.com/en-us/library/ms187928.aspx

This will involve 2 CONVERT statements.  The first one will convert the DD/MM/YYYY date string into a DATETIME data type.  The second one will convert the DATETIM data type to a string/varchar with your specified format.  Here's how to do it:

SELECT CONVERT(VARCHAR(10), CONVERT(datetime,'24/01/2010',103), 111)

For other date formats, you can refer to the following link:

http://www.sql-server-helper.com/tips/date-formats.aspx

Regards,
SQL Server Helper

Thank you this actually worked...

DECLARE

@Date VARCHAR(12)

SET @Date= '22/01/2010'

 

SET

 

@Date= SUBSTRING (@Date,7,4) + '/' + SUBSTRING(@Date,4,2) + '/' + SUBSTRING(@Date,1,2)

 

SELECT

 

@Date


ItuKosh

 

Yes, you can also do it that way, using string manipulation.  Here's another way to do it using string manipulation using a combination of the LEFT, RIGHT and SUBSTRING string functions:

DECLARE @Date VARCHAR(10)
SET @Date= '22/01/2010'
SET @Date= RIGHT(@Date, 4) + '/' + SUBSTRING(@Date,4,2) + '/' + LEFT(@Date,2)
SELECT @Date

Regards,
SQL Server Helper

Your first post stated that you received an error that you "cannot convert string to datetime" when inserting.  Your "solution" converts the string to another string, which would then require another (implicit) conversion to update a datetime column.  If the column is datetime, as you wrote, all you need is the formula from my reply, e.g.:
INSERT dbo.YourTable(YourDatetimeColumn) VALUES(CONVERT(datetime,@Date,103));