How to convert DateTime to VarChar

Question

I need to convert a value which is in a DateTime variable into a varchar variable formatted as yyyy-mm-dd format (without time part).

How do I do that?

Sanctitude

CONVERT, see MSDN documentation. – Saxon

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

Answer 1

With Microsoft Sql Server:

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

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

Labia

For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (#60167) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate. – Spinner

Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way. – Collings

Is there any reason to use LEFT(.., 10) instead of CONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based on FORMAT(date_value, format_string) function. – Ardoin

@Ardoin besides FORMAT() is too slow relative to convert(char(10),...) – Ruscher

Answer 2

Here's some test sql for all the styles.

DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style 
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 to 99 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style

Here's the result

output                   style
Apr 28 2014  9:31AM          0
04/28/14                     1
14.04.28                     2
28/04/14                     3
28.04.14                     4
28-04-14                     5
28 Apr 14                    6
Apr 28, 14                   7
09:31:28                     8
Apr 28 2014  9:31:28:580AM   9
04-28-14                     10
14/04/28                     11
140428                       12
28 Apr 2014 09:31:28:580     13
09:31:28:580                 14
2014-04-28 09:31:28          20
2014-04-28 09:31:28.580      21
04/28/14  9:31:28 AM         22
2014-04-28                   23
09:31:28                     24
2014-04-28 09:31:28.580      25
Apr 28 2014  9:31AM          100
04/28/2014                   101
2014.04.28                   102
28/04/2014                   103
28.04.2014                   104
28-04-2014                   105
28 Apr 2014                  106
Apr 28, 2014                 107
09:31:28                     108
Apr 28 2014  9:31:28:580AM   109
04-28-2014                   110
2014/04/28                   111
20140428                     112
28 Apr 2014 09:31:28:580     113
09:31:28:580                 114
2014-04-28 09:31:28          120
2014-04-28 09:31:28.580      121
2014-04-28T09:31:28.580      126
2014-04-28T09:31:28.580      127
28 جمادى الثانية 1435  9:31:28:580AM    130
28/06/1435  9:31:28:580AM    131

Make nvarchar(max) shorter to trim the time. For example:

select convert(nvarchar(11), GETDATE(), 0)
union select convert(nvarchar(max), GETDATE(), 0)

outputs:

May 18 2018
May 18 2018  9:57AM

Cougar

Here Sometimes we want like dd-mm or jun - 28. There is any option?? – Voroshilov

Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited! – Euterpe

I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ? – Carpel

Isn't the timezone identifier Z missing in style 127. – Menon

@Menon "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at learn.microsoft.com/de-de/sql/t-sql/functions/… – Cougar

I Suggest not to use nvarchar(max) instead use varchar(50) or some suitable value. Otherwise, it might cause some errors in some systems. In my case, Crystal report can't detect nvarchar(max) fields As Dynamic Parameter – Icefall

Answer 3

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

Admass

Answer 4

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

Kirst

Answer 5

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)

Terisateriyaki

Answer 6

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

Slipover

Answer 7

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

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

Isostasy

Answer 8

With Microsoft SQL Server:

Use Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example:

SELECT CONVERT(varchar,d.dateValue,1-9)

For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).

Vizier

Answer 9

For SQL Server 2008+ You can use CONVERT and FORMAT together.

For example, for European style (e.g. Germany) timestamp:

CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))

Pteropod

FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE') already returns varchar with the given format, what is the reason for calling convert on it? – Cerys

It returns nvarchar, not varchar. – Pteropod

Answer 10

Try the following:

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

Ecclesiastic

Answer 11

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.

Vesicate

Answer 12

Try:

select replace(convert(varchar, getdate(), 111),'/','-');

More on ms sql tips

Promoter

Answer 13

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 )

Asparagus

Answer 14

This is how I do it:

CONVERT(NVARCHAR(10), DATE1, 103) )

Elwira

Answer 15

Try this SQL:

select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+ 
       REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')

Semiquaver

Answer 16

The shortest and the simplest way is :

DECLARE @now AS DATETIME = GETDATE()

SELECT CONVERT(VARCHAR, @now, 23)

Quarles

Answer 17

You can convert your date in many formats, the syntaxe is simple to use :

CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016

• The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.

In your case, i've just converted and restrict size by nvarchar(10) like this :

CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15

See more at : http://www.w3schools.com/sql/func_convert.asp

Another solution (if your date is a Datetime) is a simple CAST :

CAST(MY_DATE_TIME as DATE) => 2016-09-15

Renege

Answer 18

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)

Gormless

Check again: he specified sql server via a tag. – Admass

Answer 19

CONVERT(VARCHAR, GETDATE(), 23)

Obsequent

Answer 20

DECLARE @DateTime DATETIME
SET @DateTime = '2018-11-23 10:03:23'
SELECT CONVERT(VARCHAR(100),@DateTime,121 )

Semiquaver

Answer 21

select REPLACE(CONVERT(VARCHAR, FORMAT(GETDATE(), N'dd/MM/yyyy hh:mm:ss tt')),'.', '/')

will give 05/05/2020 10:41:05 AM as a result

Winer

Every resource I could find used the 'code number' to define the format, and it wasn't working for me. This one does, and in my opinion, is much better if you take a bit of time to learn the format. – Crowell

Answer 22

Simple use "Convert" and then use "Format" to get your desire date format

DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'

SELECT FORMAT(CONVERT(date, @myDateTime ),'yyyy-MM-dd')

Antifreeze

Answer 23

Write a function

CREATE FUNCTION dbo.TO_SAP_DATETIME(@input datetime)
RETURNS VARCHAR(14)
AS BEGIN
    DECLARE @ret VARCHAR(14)
    SET @ret = COALESCE(SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(26), @input, 25),'-',''),' ',''),':',''),1,14),'00000000000000');
    RETURN @ret
END

Pejoration

Answer 24

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.

Hubey

In the question, it's mentioned "I am working on a query in Sql Server 2005". – Entero

@Entero and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included. – Gamali