date convert question

In TSQL, How could I convert the following string date time into string date
format of 'mm/dd/yyyy'
Aug 23 2005 9:27AM
TIA...I just came up with this one
select convert(varchar(20),cast('Aug 23 2005 9:27AM' as datetime),101)
Any other ideas'
"sqlster" wrote:

> In TSQL, How could I convert the following string date time into string da
te
> format of 'mm/dd/yyyy'
> Aug 23 2005 9:27AM
> TIA...|||sqlster wrote:
> In TSQL, How could I convert the following string date time into
> string date format of 'mm/dd/yyyy'
> Aug 23 2005 9:27AM
> TIA...
SQL Server dates do not have formatting attached to them. When you want
to convery a string representation of a date to a SQL Server DATETIME
data type, you should always use a portable format like YYYYMMDD.
If you need the output of the query to return in a USA date format, you
can use the CONVERT function with the correct style. Style 101 should
give you what you want. Although, I would encourage you to format dates
on the client whenever possible.
Select CONVERT(CHAR(10), CAST('20050823' as DATETIME), 101) -- with
century
Select CONVERT(CHAR(10), CAST('20050823' as DATETIME), 1) -- without
century
David Gugick
Quest Software
www.imceda.com
www.quest.com|||sqlster wrote:
> In TSQL, How could I convert the following string date time into
> string date format of 'mm/dd/yyyy'
> Aug 23 2005 9:27AM
> TIA...
If you need to use a time as well, use the following portable format:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
David Gugick
Quest Software
www.imceda.com
www.quest.com