As your data already in varchar, you have to convert it into date first:

select convert(varchar(10), cast(ts as date), 101) from <your table>

The last argument of CONVERT seems to determine the format used for parsing. Consult MSDN docs for CONVERT.

111 - the one you are using is Japan yy/mm/dd.

I guess the one you are looking for is 103, that is dd/mm/yyyy.

So you should try:

 SELECT convert(datetime, '23/07/2009', 103)

The following isn’t pretty and I’m 99% there is a better way to do it, but it works. The “LEFT” function was necessary because I couldn’t find a style that didn’t include the seconds.

SELECT (CONVERT(VARCHAR(20), [yourdatetimefield], 101) + ' ' + LEFT(CONVERT(VARCHAR(20), [yourdatetimefield], 108) ,5)) AS myDateTime
  FROM [yourdb.yourtable]

SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don't want.

That task should be done by the next layer up in your software stack. SQL is a data repository, not a presentation system

You can do it with

CONVERT(VARCHAR(10), fmdate(), 101)

But you shouldn't

Give a different alias

SELECT  Convert(varchar,A.InsertDate,103) as converted_Tran_Date from table as A
order by A.InsertDate 

Hi @Raj D ,

Here is how to convert text as a DATE data type.
After that you can format it at will by using CONVERT() or FORMAT() functions.

SQL

-- DDL and sample data population, start  
DECLARE @processdata TABLE ([ProcessDate] NVARCHAR(255) NOT NULL);  
INSERT @processdata VALUES  
('Sat May 30 2020 14:19:55 GMT-0700 (Pacific Daylight Time)'),  
('Sat May 30 2020 14:19:55');  
-- DDL and sample data population, end  

  
DECLARE @separator CHAR(1) = SPACE(1);  
  
SELECT *   
 , TRY_CAST('' +   
   REPLACE([ProcessDate], @separator, '') +   
   '' AS XML)  
 .value('concat((/root/r[4]/text())[1],"-", (/root/r[2]/text())[1],"-", (/root/r[3]/text())[1])', 'DATE') AS Result  
FROM @processdata;  

;WITH rs AS  
(  
 SELECT *   
 , TRY_CAST('' +   
   REPLACE([ProcessDate], @separator, '') +   
   '' AS XML)  
 .value('concat((/root/r[4]/text())[1],"-", (/root/r[2]/text())[1],"-", (/root/r[3]/text())[1])', 'DATE') AS Result  
 FROM @processdata  
)  
SELECT *   
 , TRY_CONVERT(VARCHAR(10), rs.result, 101) AS [Converted]  
 , FORMAT(rs.result, 'MM/dd/yyyy') AS [Formatted]  
FROM rs;  

Output

+-----------------------------------------------------------+------------+  
|                        ProcessDate                        |   Result   |  
+-----------------------------------------------------------+------------+  
| Sat May 30 2020 14:19:55 GMT-0700 (Pacific Daylight Time) | 2020-05-30 |  
| Sat May 30 2020 14:19:55                                  | 2020-05-30 |  
+-----------------------------------------------------------+------------+  

Assuming your "date" column is not actually a date.

Select convert(varchar(8),cast('12/24/2016' as date),112)

or

Select format(cast('12/24/2016' as date),'yyyyMMdd')

Returns

20161224