How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2

Question

I am using CONVERT(data_type(length),expression,style) function to change the format of a date in a SELECT query.

Declare @dt nvarchar(20)

Select @dt = Convert(nvarchar(20), SalesDate, 113) FROM SalesTable

The format I need is 'dd-MMM-yyyy' (eg. '05-Jul-2013') but I could not find the proper style number (eg. 113) for this particular format. Can any one help me with that please?

It's usually better to do this formatting closer to the user (e.g. in the report generator, or in whatever other code you're writing that's presenting the results to the user) — Damien_The_Unbeliever, Aug 13 '13 at 06:47
For folks with SQL Server 2012+, you can use [`FORMAT(GETDATE(), 'dd-MMM-yyyy')`](https://stackoverflow.com/a/58925034/1366033) — KyleMit, Nov 19 '19 at 00:43

score 33 · Accepted Answer · answered Aug 13 '13 at 06:42

33

Try this:

Declare @dt NVARCHAR(20)

Select 
    @dt = REPLACE(CONVERT(CHAR(15), SalesDate, 106),' ',' - ') 
FROM SalesTable

answered Aug 13 '13 at 06:42

Abhishek Jain

2,587
1
16
12

Great Answer..exactly what I was looking for @Abhishek Jain – Saquibul Islam Waheed Aug 13 '13 at 06:56
2

Use trim functions if your date has time and you don't want to see the trailing dashes REPLACE(RTRIM(LTRIM(CONVERT(CHAR(15), SalesDate, 106))),' ','-') – Rahul Misra Jun 02 '16 at 08:33

score 14 · Answer 2 · edited May 28 '14 at 10:25

14

select CONVERT(NVARCHAR, SYSDATETIME(), 106) AS [DD-MON-YYYY]

or else

select REPLACE(CONVERT(NVARCHAR,GETDATE(), 106), ' ', '-')

both works fine

edited May 28 '14 at 10:25

alessandro

9,815
5
36
46

answered May 28 '14 at 10:03

abcd123

141
1
2

score 8 · Answer 3 · answered Aug 13 '13 at 06:55

It doesn't look like DD-MMM-YYYY is supported by default (at least, with dash as separator). However, using the AS clause, you should be able to do something like:

SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD-MON-YYYY]

See here: http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

score 5 · Answer 4 · edited Sep 24 '13 at 09:19

5

I Think this is the best way to do it.

REPLACE(CONVERT(NVARCHAR,CAST(WeekEnding AS DATETIME), 106), ' ', '-')

Because you do not have to use varchar(11) or varchar(10) that can make problem in future.

edited Sep 24 '13 at 09:19

Ankur

5,048
19
36
62

answered Sep 24 '13 at 09:01

Manish

51
1

score 1 · Answer 5 · answered Jul 04 '16 at 09:23

SELECT Convert(varchar(10),CONVERT(date,'columnname',105),105) as "end";

OR

SELECT CONVERT(VARCHAR(10), CAST(event_enddate AS DATE), 105) AS [end];

will return the particular date in the format of 'dd-mm-yyyy'

The result would be like this..

04-07-2016

score 1 · Answer 6 · edited Mar 12 '18 at 11:03

1

select convert(varchar(11), transfer_date, 106)

got me my desired result of date formatted as 07 Mar 2018

My column transfer_date is a datetime type column and I am using SQL Server 2017 on azure

edited Mar 12 '18 at 11:03

krock

27,848
12
75
84

answered Mar 12 '18 at 06:55

Hasan Junaid Hashmi

47
2

Allan F · Answer 7 · 2021-07-22T05:06:29.333

0

Other answers here seem to return columns that are quite wide.

This answer returns a varchar(11) which is all that is required for a date in dd-Mon-yyyy format.

'SalesDate' = CONVERT(VARCHAR(11),REPLACE(CONVERT(VARCHAR(11),SalesDate, 106), ' ', '-')),

Try also:

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

As of today, this gives a result as per: 22-Jul-2021

edited Jul 22 '21 at 05:06

answered Jul 16 '21 at 00:34

Allan F

1,826
1
17
26

How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2

7 Answers7

Linked