Convert Date format into DD/MMM/YYYY format in SQL Server
SqlSql ServerSql Server-2008Sql Server-2012Sql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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), ' ', '/')
Solution 2 - Sql
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:
Solution 3 - Sql
we can convert date into many formats like
SELECT convert(varchar, getdate(), 106)
This returns dd mon yyyy
More Here This may help you
Solution 4 - Sql
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.
Solution 5 - Sql
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
Solution 6 - Sql
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.
Solution 7 - Sql
You can use this query-
SELECT FORMAT (getdate(), 'dd/MMM/yy') as date
Hope, this query helps you.
Thanks!!
Solution 8 - Sql
Try this :
select replace ( convert(varchar,getdate(),106),' ','/')
Solution 9 - Sql
Anyone trying to manually enter the date to sql server 'date type' variable use this format while entering :
> 'yyyy-mm-dd'
Solution 10 - Sql
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')
Solution 11 - Sql
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)