Advertisement
Advertisement


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?

2015/09/21
1
303
9/21/2015 8:05:47 AM

Accepted Answer

With Microsoft Sql Server:

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

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
2008/09/16
262
9/16/2008 4:52:35 PM


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

2015/05/31

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')
2013/07/18

You can use DATEPART(DATEPART, VARIABLE). For example:

DECLARE @DAY INT 
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
2012/07/20

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

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


SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ; 
2011/06/21

Source: https://stackoverflow.com/questions/74385
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]