this is date: 01 08 2006
declare @d_date varchar(20)
set @d_date = '01 08 2006'

using convert:
select convert(datetime,@d_date ,120)
------
01 08 2006

replace space to '-'
select convert(datetime,replace(@d_date,' ','-') ,120)
------
01 08 2006

replace space to '/'
select convert(datetime,replace(@d_date,' ','/') ,120)
------
01 08 2006

using cast:
cast(@d_date as datetime)
------
2006-08-01 00:00:00.000

Hi
CONVERT function has a third parameter STYLE. For details please refer to
the BOL



Show quoteHide quote

I do not get the same result as you posted. See my comments with my results inline below. I executed
the code from SSMS running against SQL Server 2005 with sp1.

This one fails. Code 120 is 'yyyy-mm-dd' with optional time part. Your string has spaces and not
"-"s.


This on is OK because you first replace the spaces with "-" so the string follow code 120 rule. The
output, btw is:
-----------------------

2006-01-08 00:00:00.000

Reason for above output is that result from query is datetime datatype and SSMS is free to convert
that binary datetime information to something humany readable. If you see something different it
would be because you have some other tool and possibly specified the tool to use regional settings
when presenting datetime data.


This gives same result as above. Apparently, SQL Server tries to be friendly and accelt "/" instead
of "-" even though



First, you need SELECT in front of above. Second, above fails with conversion error. See
http://www.karaszi.com/SQLServer/info_datetime.asp

TOP 50 invoices by company
Denormalizing a database table
How can I insert a new field between existing field?
2005, Ambiguous column name, and ORDER BY
auditing changes