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).

Please help, thanks.

Answer 1

CONVERT(varchar(10), [MyDateTimecolumn], 20)

See this page for convert styles:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 2

CONVERT, see MSDN documentation.

Answer 3

Either Cast or Convert:

Syntax for CAST: CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Actually since you asked for a specific format:

REPLACE(CONVERT(varchar(10), Date, 102), ,'.', '-')

Answer 4

You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2008-09-16  | 
+-------------+
1 row in set (0.00 sec)

Answer 5

CONVERT(VARCHAR(10),GetDate(),102)

Then you would need to replace the "." with "-".

Here is a site that helps http://www.mssqltips.com/tip.asp?tip=1145

Answer 6

With Microsoft Sql Server:

--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

Answer 7

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");

Howerver, 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 8

be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post

Answer 9

declare @dt datetime

set @dt = getdate()

select convert(char(10),@dt,120) --fixed data length of char(10) as you want a specific string format.