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?

be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see [this blog post](http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/05/date-format-inconsistency-yyyy-mm-dd-or-yyyymmdd-when-2007-04-10-is-4th-october.aspx) — Andy Irving, Sep 16 '08 at 17:49
`CONVERT`, see [MSDN documentation](http://msdn.microsoft.com/en-us/library/ms187928.aspx). — Jason Cohen, Sep 16 '08 at 16:46

Colin · Answer 1 · 2018-05-18T09:59:54.280

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

Here Sometimes we want like `dd-mm` or `jun - 28`. There is any option?? — Rock, May 24 '16 at 14:32
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! — dyslexicanaboko, Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ? — ahmed abdelqader, Nov 10 '16 at 07:35
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at https://learn.microsoft.com/de-de/sql/t-sql/functions/cast-and-convert-transact-sql — Colin, Sep 04 '17 at 11:15
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 — Menuka Ishan, Jul 30 '18 at 10:19

score 274 · Accepted Answer · answered Sep 16 '08 at 16:52

274

With Microsoft Sql Server:

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

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

answered Sep 16 '08 at 16:52

TonyOssa

3,362
1
18
14

12

For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (http://stackoverflow.com/questions/59667/why-would-i-ever-pick-char-over-varchar-in-sql) 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. – Will Ediger Dec 30 '14 at 23:04
1

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. – Alex Nolasco May 15 '15 at 16:07
3

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](http://stackoverflow.com/a/17713768/1026) suggesting a solution based on `FORMAT(date_value, format_string)` function. – Nickolay Jan 25 '17 at 15:28
1

@Nickolay besides `FORMAT()` is too slow relative to `convert(char(10),...)` – abdul qayyum Nov 02 '17 at 10:08

score 188 · Answer 3 · edited May 31 '15 at 13:09

188

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

edited May 31 '15 at 13:09

Yakir Manor

4,687
1
32
25

answered Sep 16 '08 at 16:45

Joel Coehoorn

399,467
113
570
794

score 40 · Answer 4 · answered Jul 18 '13 at 02:58

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

score 9 · Answer 5 · edited Jul 20 '12 at 21:47

9

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)

edited Jul 20 '12 at 21:47

Kijewski

25,517
12
101
143

answered Jul 20 '12 at 21:42

FCKOE

91
1
1

score 8 · Answer 6 · edited May 11 '11 at 22:34

8

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

edited May 11 '11 at 22:34

BlueRaja - Danny Pflughoeft

84,206
33
197
283

answered Sep 16 '08 at 16:46

Per Hornshøj-Schierbeck

15,097
21
80
101

P's-SQL · Answer 7 · 2011-06-21T19:04:30.653

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

edited Jun 21 '11 at 19:04

answered Jun 21 '11 at 18:45

P's-SQL

71
1
2

score 6 · Answer 8 · edited Oct 20 '12 at 08:51

6

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

edited Oct 20 '12 at 08:51

Pete Carter

2,691
3
23
34

answered May 30 '12 at 15:44

dmunozpa

131
1
7

Peter Majko · Answer 9 · 2018-11-26T17:54:43.100

4

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

edited Nov 26 '18 at 17:54

answered Nov 26 '18 at 17:28

Peter Majko

1,241
11
22

`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? – Alexandru Severin Jun 22 '22 at 10:52
It returns nvarchar, not varchar. – Peter Majko Sep 01 '22 at 20:20

score 3 · Answer 10 · edited Apr 22 '14 at 07:22

3

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.

edited Apr 22 '14 at 07:22

Dawson Loudon

6,029
2
27
31

answered Sep 26 '08 at 09:33

Andy Jones

1,472
9
15

score 3 · Answer 11 · edited Aug 11 '13 at 20:50

3

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

edited Aug 11 '13 at 20:50

FabianCook

20,269
16
67
115

answered Mar 25 '13 at 17:31

IvanSnek

106
1
4

score 3 · Answer 12 · edited Jul 10 '17 at 08:54

3

Try this SQL:

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

edited Jul 10 '17 at 08:54

Andre

26,751
7
36
80

answered Jul 10 '17 at 06:29

Dilkhush

41
2

score 3 · Answer 13 · answered Jun 06 '11 at 15:32

3

Try:

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

More on ms sql tips

answered Jun 06 '11 at 15:32

Arek Bee

329
3
9

score 3 · Answer 14 · answered Aug 12 '11 at 13:38

3

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 )

answered Aug 12 '11 at 13:38

OldBuildingAndLoan

2,801
4
32
40

score 3 · Answer 15 · edited Apr 14 '14 at 10:54

3

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

edited Apr 14 '14 at 10:54

DMK

2,448
1
24
35

answered Sep 16 '08 at 16:50

Amy Patterson

679
1
10
24

score 2 · Answer 16 · answered Dec 01 '14 at 15:44

2

The shortest and the simplest way is :

DECLARE @now AS DATETIME = GETDATE()

SELECT CONVERT(VARCHAR, @now, 23)

answered Dec 01 '14 at 15:44

Konstantin

3,294
21
23

Ema.H · Answer 17 · 2017-01-11T15:43:19.937

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

score 1 · Answer 18 · edited Apr 29 '14 at 16:47

1

CONVERT(VARCHAR, GETDATE(), 23)

edited Apr 29 '14 at 16:47

Ashkan Mobayen Khiabani

33,575
33
102
171

answered Apr 29 '14 at 16:13

Gabriel

595
7
10

score 1 · Answer 19 · edited Nov 23 '18 at 04:50

1

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

edited Nov 23 '18 at 04:50

K.Dᴀᴠɪs

9,945
11
33
43

answered Nov 23 '18 at 04:30

Dilkhush

41
2

score 1 · Answer 20 · edited May 05 '20 at 17:38

1

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

edited May 05 '20 at 17:38

ihebiheb

3,673
3
46
55

answered May 05 '20 at 15:39

Andres Galindo

11
1

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. – Engels Leonhardt Mar 24 '22 at 21:11

score 1 · Answer 21 · answered Sep 16 '08 at 16:49

1

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)

answered Sep 16 '08 at 16:49

Allan Wind

23,068
5
28
38

1

Check again: he specified sql server via a tag. – Joel Coehoorn Sep 16 '08 at 16:51

score 0 · Answer 22 · answered Aug 02 '19 at 08:43

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

score 0 · Answer 23 · answered Apr 23 '21 at 07:47

0

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

answered Apr 23 '21 at 07:47

Zain

272
2
11

score -3 · Answer 24 · edited Nov 25 '15 at 11:14

-3

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.

edited Nov 25 '15 at 11:14

Sabyasachi Mishra

1,677
2
31
49

answered Sep 16 '08 at 16:52

Johnny Bravado

105
2

In the question, it's mentioned "I am working on a query in Sql Server 2005". – InkHeart Aug 23 '16 at 01:04
4

@InkHeart 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. – GSazheniuk Apr 06 '17 at 21:33

How to convert DateTime to VarChar

24 Answers24

Linked

Related