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.