Convert date format to DD / MMM / YYYY format in SQL Server

Question

I have a query in sql, I need to get the date in dd/mmm/yy format

Example: 25/jun/2013 .

How can I convert it to SQL server?

Answer 1

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

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

Answer 2

There are already several answers and formatting types for SQL Server 2008. But this method is somewhat ambiguous, and it will be difficult for you to remember the number in relation to a specific date format. This is why there is a better option in future versions of SQL Server.

If you are using SQL Server 2012 or later, you must use the Format () function

 FORMAT ( value, format [, culture ] ) 

With culture settings, you can specify a date for 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日 

To solve the OP, we can use the following format that @Martin Smith already mentioned:

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

Some examples of date formats:

If you need more SQL server date formats, you should visit:

• Custom date and time format
• Standard date and time format

Answer 3

we can convert the date to many formats, for example

 SELECT convert(varchar, getdate(), 106) 

This returns dd mon yyyy

Read more here. It can help you.

Answer 4

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

It should be noted that the returned results depend, however, on the input language.

 SET language Russian SELECT replace(CONVERT(NVARCHAR, getdate(), 106), ' ', '/') 

Returns

 06//2015 

at the time of writing.

For people facing this issue in later versions of SQL Server, a method that avoids this problem, and the need for REPLACE is

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

In 2005, you could write a CLR UDF that would adopt DateTime, a formatting pattern, and culture to mimic the same thing.

Answer 5

Try using the following query.

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

Result: 20 / June / 13

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

Result: 20 / June / 2013

Answer 6

try it

 select convert(varchar,getdate(),100) 

The third parameter is the format, the range is from 100 to 114 , any of them should work for you.

If you need a date in dd/mmm/yyyy , use the following command:

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

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

Answer 7

Try the following:

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

Answer 8

Anyone who tries to manually enter the date into the sql server variable uses this format when entering:

'yyyy-mm-dd'

Answer 9

You can use this query-

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

Hope this query helps you.

Thank!!