Convert DateTime To YYYY-MM-DD Format In SQL Server

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 Long 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 Long 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.