How do I convert a datetime field to a formatted string in SQL Server 2005?

Question

I want to SELECT a formatted date string from a datetime type in SQL Server 2005.

In the format "yyyy/mm/dd hh:mm:ss".

What is the best way to do using only a query?

Answer 1

Check out the CONVERT statement.

SELECT CONVERT(VARCHAR(20), getdate(), 120)

is closest to what you want. (Note the different separators (- instead of / ))

Answer 2

this link has what all the different convert styles are.

Answer 3

 select convert(varchar, datetime_field, 120) from tablename;

will do almost what you want.

120 is the conversion "style", see here for more.