converting date to DD-MMM-YYYY format

Question

I'm using following line of code in SQL:

REPLACE(CONVERT(VARCHAR(11),getdate(),106), ' ','-')

to convert given date to MMM-DD-YYYY format, this code is working perfectly fine if current date is more that 10th.

But I'm getting error when its tries to convert single digit date, e.g when I'm converting date 9 jan 2014. It is converted to Jan--9-2014.

Which is actually wrong as its contain one extra -.

Please help me to resolve this issue.

Just in case, what if you use replace double spaces with single spaces before replacing spaces with dashes? — user1455836, Jan 09 '14 at 09:53
But [it is working fine for `9 jan 2014` here](http://www.sqlfiddle.com/#!3/d41d8/27927)? — Mahmoud Gamal, Jan 09 '14 at 09:54
showing correct date when executing.. select REPLACE(CONVERT(VARCHAR(11),getdate(),106), ' ','-') shows 08-Jan-2014 current date here.. — Suraj Shrestha, Jan 09 '14 at 09:57
except for question author expects month first, not day. And also wants day in one digit if it's in range 1-9 — user1455836, Jan 09 '14 at 09:59
select REPLACE(cast(getDate() as varchar(11)), ' ','-') returns Jan--8-2014 check if you are using cast or convert.. — Suraj Shrestha, Jan 09 '14 at 10:02
Does this answer your question? [How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2](https://stackoverflow.com/questions/18202260/how-to-get-a-particular-date-format-dd-mmm-yyyy-in-select-query-sql-server-2) — KyleMit, Nov 19 '19 at 00:43

score 2 · Answer 1 · answered Apr 29 '19 at 06:02

2

CONVERT DATE IN dd-mmm-yyyy FORMAT (Eg. 01-Jun-2018)

REPLACE(CONVERT(CHAR(11), DOB, 106),' ','-')  AS DOB

answered Apr 29 '19 at 06:02

Code

679
5
9

score 2 · Answer 2 · answered Nov 19 '19 at 00:40

As of SQL Server 2012+, you can use FORMAT(value, format [, culture ])

Where the format param takes any valid standard format string or custom formatting string

Example:

SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy')

Further Reading:

score -1 · Answer 3 · edited Jul 08 '14 at 18:07

-1

Try this

SELECT CONVERT(varchar(11),getdate(),103)

see this link for more details

edited Jul 08 '14 at 18:07

ann

576
1
10
19

answered Jan 09 '14 at 09:49

Ajay

6,418
18
79
130

score -1 · Answer 4 · answered Jan 09 '14 at 09:54

-1

Try This.

select REPLACE(REPLACE(CONVERT(VARCHAR,getdate(),107), ' ','-'), ',','')

answered Jan 09 '14 at 09:54

Janty

1,708
2
15
29

user1455836 · Answer 5 · 2014-01-09T10:12:55.660

-1

select REPLACE(replace(cast(getDate() as varchar(11)), '  ', ' '), ' ','-')

returns correct result Jan-9-2014 on your fiddle

here is the fiddle

edited Jan 09 '14 at 10:12

answered Jan 09 '14 at 10:06

user1455836

752
6
18

It still returns `Jan--9-2014` – Vishal Suthar Jan 09 '14 at 10:08

score -1 · Answer 6 · answered Jan 09 '14 at 10:19

-1

How about this:

select REPLACE(REPLACE(REPLACE(cast(getDate() as varchar(11)),' ','{}'),'}{',''),'{}','-')

Output: Jan-9-2014

SQL Fiddle Demo

answered Jan 09 '14 at 10:19

Vishal Suthar

17,013
3
59
105

score -1 · Answer 7 · answered Jan 10 '14 at 18:59

-1

What about

select left(DATEname(m,getdate()),3)+'-'+  
cast(DAY(getdate()) as varchar)+'-'+ 
CAST(year(getdate()) as varchar)

?

answered Jan 10 '14 at 18:59

msi77

1,602
1
11
10

converting date to DD-MMM-YYYY format

7 Answers7