How to convert DateTime to VarChar

Question

I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?

Answer 1

SQL Server 2012 has a new function , FORMAT: http://msdn.microsoft.com/en-us/library/ee634924.aspx

and you can use custom date time format strings: http://msdn.microsoft.com/en-us/library/ee634398.aspx

These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.

Example usage (Australian datetime):

FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')

Answer 2

You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:

DateTime d = DateTime.Today;
string result = d.ToString("yyyy-MM-dd");

However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.

Answer 3

This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )