Convert Date format into DD/MMM/YYYY format in SQL Server

Question

I have a query in SQL, I have to get a date in a format of dd/mm/yy

Example: 25/jun/2013.

How can I convert it for SQL server?

possible duplicate of http://stackoverflow.com/questions/15017300/how-to-convert-the-system-date-format-to-dd-mm-yy-in-sql-server-2008-r2?rq=1 — Anand, Jun 20 '13 at 04:48
i got the format as 25/06/2013 but what i need is 25/Jun/2013 — Krish KvR, Jun 20 '13 at 04:49

score 69 · Accepted Answer · answered Jun 20 '13 at 04:50

69

I'm not sure there is an exact match for the format you want. But you can get close with convert() and style 106. Then, replace the spaces:

SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/')

answered Jun 20 '13 at 04:50

Jonathan Lonowski

121,453
34
200
199

3

Just to mention, code 103 directly gives requested date format with just numbers. – Rajiv Mar 10 '17 at 11:57

score 49 · Answer 2 · edited Jun 20 '20 at 09:12

There are already multiple answers and formatting types for SQL server 2008. But this method somewhat ambiguous and it would be difficult for you to remember the number with respect to Specific Date Format. That's why in next versions of SQL server there is better option.

If you are using SQL Server 2012 or above versions, you should use Format() function

FORMAT ( value, format [, culture ] )

With culture option, you can specify date as per your viewers.

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; 
  
SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';

US English Result Great Britain English Result  German Result Simplified Chinese (PRC) Result
----------------  ----------------------------- ------------- -------------------------------------
10/1/2011         01/10/2011                    01.10.2011    2011/10/1

US English Result            Great Britain English Result  German Result                    Chinese (Simplified PRC) Result
---------------------------- ----------------------------- -----------------------------  ---------------------------------------
Saturday, October 01, 2011   01 October 2011               Samstag, 1. Oktober 2011        2011年10月1日

For OP's solution, we can use following format, which is already mentioned by @Martin Smith:

FORMAT(GETDATE(), 'dd/MMM/yyyy', 'en-us')

Some sample date formats:

If you want more date formats of SQL server, you should visit:

score 31 · Answer 3 · answered Jun 20 '13 at 04:51

31

we can convert date into many formats like

SELECT convert(varchar, getdate(), 106)

This returns dd mon yyyy

More Here This may help you

answered Jun 20 '13 at 04:51

Nithesh Narayanan

11,481
34
98
138

score 11 · Answer 4 · answered Apr 06 '15 at 15:40

The accepted answer already gives the best solution using built in formatting methods in 2008.

It should be noted that the results returned is dependent on the language of the login however.

SET language Russian

SELECT replace(CONVERT(NVARCHAR, getdate(), 106), ' ', '/')

Returns

06/апр/2015

at the time of writing.

For people coming across this question on more recent versions of SQL Server a method that avoids this issue - and the need to REPLACE is

FORMAT(GETDATE(),'dd/MMM/yyyy', 'en-us')

On 2005+ it would be possible to write a CLR UDF that accepted a DateTime, Formatting Pattern and Culture to simulate the same.

score 10 · Answer 5 · answered Jun 20 '13 at 04:51

10

Try using the below query.

SELECT REPLACE(CONVERT(VARCHAR(11),GETDATE(),6), ' ','/');

Result: 20/Jun/13

SELECT REPLACE(CONVERT(VARCHAR(11),GETDATE(),106), ' ','/');

Result: 20/Jun/2013

answered Jun 20 '13 at 04:51

Praveen

55,303
33
133
164

score 2 · Answer 6 · edited Feb 14 '15 at 14:49

2

Try this

select convert(varchar,getdate(),100)

third parameter is format, range is from 100 to 114, any one should work for you.

If you need date in dd/mmm/yyyy use this:

replace(convert(char(11),getdate(),113),' ','-')

Replace getdate() with your column name. This worked for me.

edited Feb 14 '15 at 14:49

Reza

18,865
13
88
163

answered Sep 14 '13 at 05:08

Chethan

298
1
4
17

score 2 · Answer 7 · answered Jun 20 '19 at 13:09

2

You can use this query-

SELECT FORMAT (getdate(), 'dd/MMM/yy') as date

Hope, this query helps you.

Thanks!!

answered Jun 20 '19 at 13:09

Nitika Chopra

1,281
17
22

score 1 · Answer 8 · edited Dec 04 '15 at 11:26

1

Try this :

select replace ( convert(varchar,getdate(),106),' ','/')

edited Dec 04 '15 at 11:26

Ajay2707

5,690
6
40
58

answered Dec 04 '15 at 11:23

KD29

109
1
3
15

score 0 · Answer 9 · answered Sep 07 '17 at 09:59

0

Anyone trying to manually enter the date to sql server 'date type' variable use this format while entering :

'yyyy-mm-dd'

answered Sep 07 '17 at 09:59

IteratioN7T

363
8
21

score 0 · Answer 10 · answered Jul 30 '20 at 05:53

Just format after convert to a date (tested in SQL Server v.2017)

dd/mon/yyyy
Select Format(cast('25/jun/2013' as date),'dd/MMM/yyyy')

dd/mm/yyyy
Select Format(cast('25/jun/2013' as date),'dd/MM/yyyy')

dd/Month/yyyy
Select Format(cast('25/jun/2013' as date),'dd/MMMM/yyyy')

score 0 · Answer 11 · answered Oct 22 '20 at 11:04

Simply get date and convert

Declare @Date as Date =Getdate()

Select Format(@Date,'dd/MM/yyyy') as [dd/MM/yyyy] // output: 22/10/2020
Select Format(@Date,'dd-MM-yyyy') as [dd-MM-yyyy] // output: 22-10-2020

//string date
Select Format(cast('25/jun/2013' as date),'dd/MM/yyyy') as StringtoDate // output: 25/06/2013

Source: SQL server date format and converting it (Various examples)

score 0 · Answer 12 · answered Jun 19 '23 at 06:16

There are already very good answers available like:

select replace(convert(varchar,getdate(),106),' ','/')

But, I want to add a comprehensive list of codes for all the other formats:

Here is the the link:

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

Convert Date format into DD/MMM/YYYY format in SQL Server

12 Answers12

If you are using SQL Server 2012 or above versions, you should use Format() function

Linked

Related