Format Datetime in T-SQL

Method 1: Using the CONVERT function:

SELECT CONVERT(VARCHAR(10), your_datetime_field, 120) AS formatted_date
FROM your_table;

• 120: This style code specifies the format as YYYY-MM-DD.
• your_datetime_field: Replace this with the actual name of your datetime field.
• VARCHAR(10): Specifies the output data type as a character string with a length of 10 characters.
• CONVERT: Converts the value to the specified data type.

SELECT CAST(your_datetime_field AS DATE) AS formatted_date
FROM your_table;

• DATE: Specifies the output data type as a date.

Example:

SELECT CONVERT(VARCHAR(10), OrderDate, 120) AS OrderDate_YYYY_MM_DD
FROM Orders;

This query will return the OrderDate column from the Orders table in the YYYY-MM-DD format.

Additional notes:

• If you need to include the time portion in the output, you can adjust the format code or data type accordingly.
• The CAST method is generally more concise and easier to read, especially if you only need the date portion.
• Both methods will produce the same result.

Getting a Date in YYYY-MM-DD Format from a T-SQL datetime Field

SELECT CONVERT(VARCHAR(10), your_datetime_field, 120) AS formatted_date
FROM your_table;

SELECT CONVERT(VARCHAR(10), OrderDate, 120) AS OrderDate_YYYY_MM_DD
FROM Orders;

SELECT CAST(your_datetime_field AS DATE) AS formatted_date
FROM your_table;

SELECT CAST(OrderDate AS DATE) AS OrderDate_YYYY_MM_DD
FROM Orders;

Formatting Datetime in T-SQL

Using the CONVERT function with different style codes:

• 107: yyyy-mm-dd hh:mi:ss.mmm(precision)
• 106: yyyy-mm-dd hh:mi:ss.mmm
• 104: yyyy-mm-dd
• 103: dd/mm/yyyy
• 101: mm/dd/yyyy

SELECT CONVERT(VARCHAR(10), OrderDate, 104) AS OrderDate_YYYY_MM_DD
FROM Orders;

Using the DATEPART function to extract individual components:

SELECT DATEPART(year, OrderDate) AS OrderYear,
       DATEPART(month, OrderDate) AS OrderMonth,
       DATEPART(day, OrderDate) AS OrderDay
FROM Orders;

You can then concatenate these components using string concatenation operators (e.g., + or CONCAT) to create the desired format.

Alternative Methods for Formatting Dates in T-SQL

Using FORMAT function (SQL Server 2012 and later)

This function provides a more flexible way to format dates and numbers. You can specify a custom format string to achieve the desired output.

SELECT FORMAT(OrderDate, 'yyyy-MM-dd') AS OrderDate_YYYY_MM_DD
FROM Orders;

Using DATEADD and DATEDIFF functions

You can use these functions to manipulate dates and extract specific parts. For example, to get the first day of the month:

SELECT DATEADD(day, 1 - DATEPART(day, OrderDate), OrderDate) AS FirstDayOfMonth
FROM Orders;

Using string manipulation functions

If you need more complex formatting or customization, you can use string functions like LEFT, RIGHT, and SUBSTRING to extract and manipulate parts of the date string.

SELECT LEFT(CONVERT(VARCHAR(10), OrderDate, 101), 7) + RIGHT(CONVERT(VARCHAR(10), OrderDate, 101), 3) AS OrderDate_YYYY_MM_DD
FROM Orders;

Using custom functions

If you frequently need to format dates in a specific way, you can create a custom function to encapsulate the logic.

CREATE FUNCTION dbo.FormatDate(@date datetime)
RETURNS VARCHAR(10)
AS
BEGIN
    RETURN CONVERT(VARCHAR(10), @date, 120);
END
GO

SELECT dbo.FormatDate(OrderDate) AS OrderDate_YYYY_MM_DD
FROM Orders;

Choosing the best method:

• If you need to reuse a specific formatting logic, creating a custom function can be helpful.
• For more complex manipulations, DATEADD, DATEDIFF, and string functions can be used.
• For simple formatting, CONVERT or FORMAT are usually sufficient.