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?

Snowberry

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) – Reuven

For folks with SQL Server 2012+, you can use FORMAT(GETDATE(), 'dd-MMM-yyyy') – Laterite

Answer 1

Try this:

Declare @dt NVARCHAR(20)

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

Cultural

Great Answer..exactly what I was looking for @Abhishek Jain – Snowberry

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))),' ','-') – Yuji

Answer 2

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

or else

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

both works fine

Greeson

Answer 3

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

Custos

Answer 4

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.

Roche

Answer 5

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

Microwatt

Answer 6

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

Billfish

Answer 7

select FORMAT(getdate(), 'dd-MMM-yyyy') as DateToday

Milissamilissent

Answer 8

SELECT empl_id, empl_name, FORMAT(empl_dob,'dd-MMM-yyyy') FROM [company_XYZ].[dbo].[tbl_empl]

The 'empl_dob' is the date field which is being formatted in 'dd-MMM-yyyy' for example (15-JAN-1984) of the database 'company_XYZ' and table 'tbl_empl'.

Rimrock

Answer 9

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

Prothalamion