16

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?

Community
  • 1
  • 1
Saquibul Islam Waheed
  • 460
  • 2
  • 5
  • 17
  • 2
    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
  • 1
    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

9 Answers9

33

Try this:

Declare @dt NVARCHAR(20)

Select 
    @dt = REPLACE(CONVERT(CHAR(15), SalesDate, 106),' ',' - ') 
FROM SalesTable
Abhishek Jain
  • 2,597
  • 1
  • 18
  • 12
14
select CONVERT(NVARCHAR, SYSDATETIME(), 106) AS [DD-MON-YYYY]

or else

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

both works fine

ale
  • 10,012
  • 5
  • 40
  • 49
abcd123
  • 141
  • 1
  • 2
8

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

Brett
  • 165
  • 6
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.

Ankur
  • 5,086
  • 19
  • 37
  • 62
Manish
  • 51
  • 1
1
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
Deepak Keynes
  • 2,291
  • 5
  • 27
  • 56
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

krock
  • 28,904
  • 13
  • 79
  • 85
Hasan Junaid Hashmi
  • 45
  • 2
1

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

Mahfuj Ur Rahman
  • 51
  • 4
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

Allan F
  • 2,110
  • 1
  • 24
  • 29
0
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'.

Dr. Nand Kumar
  • 1
  • 1