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)

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

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

I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order. (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)

Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.

http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT

I changed the month to 8 to make it easier to double check. Using the CONVERT style option 3, you can do the following:

DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809'; 

-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
     SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);

-- Using the DATE 
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;    
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];

The results of the last two selects are:

DefaultFormat
-------------
2009-08-25

DD/MM/YY
--------
25/08/09

To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)

Likewise when displaying the DATE as you desire, you need to use STYLE 3

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]

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

SELECT CONVERT(VARCHAR(10),@date, 120)
--Gives: yyyy-mm-dd

The error is happening because you (or whoever designed this table) have a bunch of dates in VARCHAR. Why are you (or whoever designed this table) storing dates as strings? Do you (or whoever designed this table) also store salary and prices and distances as strings?

To find the values that are causing issues (so you (or whoever designed this table) can fix them):

SELECT GRADUATION_DATE FROM mydb
  WHERE ISDATE(GRADUATION_DATE) = 0;

Bet you have at least one row. Fix those values, and then FIX THE TABLE. Or ask whoever designed the table to FIX THE TABLE. Really nicely.

ALTER TABLE mydb ALTER COLUMN GRADUATION_DATE DATE;

Now you don't have to worry about the formatting - you can always format as YYYYMMDD or YYYY-MM-DD on the client, or using CONVERT in SQL. When you have a valid date as a string literal, you can use:

SELECT CONVERT(CHAR(10), CONVERT(datetime, '20120101'), 120);

...but this is better done on the client (if at all).

There's a popular term - garbage in, garbage out. You're never going to be able to convert to a date (never mind convert to a string in a specific format) if your data type choice (or the data type choice of whoever designed the table) inherently allows garbage into your table. Please fix it. Or ask whoever designed the table (again, really nicely) to fix it.

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 |  
+-----------------------------------------------------------+------------+  

Try this:

DECLARE @Date1 VARCHAR(50) = '30-01-2015'

SELECT CONVERT(VARCHAR(10), CONVERT(date, @Date1, 105), 23)