Posted by Erland Sommarskog on 06/08/06 06:54

SQL Server (alderran666@gmail.com) writes:
> All I want to know is how to return
> 08/29/2006
>
> from
> '2006-08-29 00:00:00.000'
>
> Looking at the SQL Server Books Online help resource it appears to me
> that the convert function should be able to do this. But this doesn't
> work. Why not and how can I format that date the way I want in the
> output. In VB I'd just use the format function. Is there something
> similar in T-SQL?
> print convert(datetime, '2006-08-29 00:00:00.000', 101)

That converts a string value to datetime. You want to convert a datetime
value to a string.

A datetime value is a internally a numeric value and does not have any
format. The format code in the above example tells SQL Server how to
interpret the string.

But as I said, while you can format date values to string in your SQL code,
you should avoid doing so. This should be done client-side, so that the
client's regional settings can be respected. I can tell you that if you
give me an app that spits out strings like 08/29/2006, you will have a bug
report back in ten seconds, because that is not a date as far as I'm
concerned.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]