Advertisement
Advertisement


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?

2019/12/27
1
60
12/27/2019 12:09:29 PM

Accepted Answer

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), ' ', '/')
2013/06/20
66
6/20/2013 4:50:27 AM


we can convert date into many formats like

SELECT convert(varchar, getdate(), 106)

This returns dd mon yyyy

More Here This may help you

2013/06/20

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

2013/06/20

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.

2015/04/06

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.

2015/02/14

Try this :

select replace ( convert(varchar,getdate(),106),' ','/')
2015/12/04