Frequently, you may need to convert the datetime value to a specific formatted date like YYYY-MM-DD. Before SQL Server 2012, we used CONVERT to format the date. In SQL Server 2012, Microsoft introduced a built-in string function called FORMAT. Using FORMAT you can format datetime value as you wish. Let’s see how to use FORMAT function to format date.
Syntax
FORMAT ( <value>, <format>) Where: <value> = In our case this is a datetime or date calue, <format> = In our case it's 'yyyy-MM-dd'. NOTE: Make sure yyyy in lower case, MM in upper case dd in lower case. Returns: Formatted date in nvarchar format.
Example
DECLARE @date DateTime; SET @date = GetDate() SELECT FORMAT(@date, 'yyyy-MM-dd') GO /* Result */ 2019-06-27

Other Date Formats
You can use the FORMAT function, to format the datetime value is several other ways you like. Here is some of the examples.
DECLARE @date DateTime; SET @date = GetDate() SELECT FORMAT(@date, 'yyyy-MM-dd hh-mm-ss') As 'Date & Time', FORMAT(@date, 'MM/dd/yyyy') As 'Date in US format', FORMAT(@date, 'yyyy/MM/dd') As 'Date' GO /* Result */ Date and Time Date in US format Date ----------------------- ------------------ ----- 2019-06-27 01-38-02 06/27/2019 2019/06/27
Culture Based Date Formatting
In SQL Server 2017. Microsoft enhanced the FORMAT built-in function to include culture based formatting. This can be done with the optional third parameter. Here is the syntax and example of culture based formatting of date:
Syntax
FORMAT ( <value>, <format> [, <culture> ] ) Where: <value> = In our case this is a datetime or date calue, <format> = In our case it is lower case 'd' for short date time format or upper case 'D' for longer version. <culture> = culture Returns: Formatted date in nvarchar format.
Example
DECLARE @date DateTime; SET @date = GetDate() SELECT FORMAT(@date, 'd', 'en-US') As 'US Short Date', FORMAT(@date, 'D', 'en-US') As 'US Long Date', FORMAT(@date, 'd', 'en-GB') As 'British Short Date', FORMAT(@date, 'D', 'en-GB') As 'British Long Date', FORMAT(@date, 'd', 'en-IN') As 'Indian Short Date', FORMAT(@date, 'D', 'en-IN') As 'Indian Long Date' GO /* Result */ US Short Date US Long Date British Short Date British Long Date India Short Date India Long Date 6/27/2019 Thursday, June 27, 2019 27/06/2019 27 June 2019 27-06-2019 27 June 2019
Reference
- About built-in string function FORMAT at Microsoft Docs.
To use Convert with Date code “101”,”1″ to convert date times
Example:
Select CONVERT(varchar, SYSDATETIME(), 1) AS [USAFormat]
// output-> 10/22/2020
Source
You have the British Long date written twice.
Thank you,
I’ve corrected it now.