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

declare @dt datetime

set @dt = getdate()

select convert(char(10),@dt,120) 

I have fixed data length of char(10) as you want a specific string format.

Answer 2

Try the following:

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

For a full date time and not just date do:

CONVERT(varchar(23), [MyDateTimecolumn], 121)

See this page for convert styles:

http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function

Answer 3

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 4

The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.

convert( varchar(10), convert( date, @yourDate ) , 111 )

Answer 5

The shortest and the simplest way is :

DECLARE @now AS DATETIME = GETDATE()

SELECT CONVERT(VARCHAR, @now, 23)

Answer 6

-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'

SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;