The article is originally published to my blog StackBlogger as MS SQL Server: Select Custom Formatted Date Time
MS SQL Server provides several built-in functions to format and display dates in different ways. One of the most commonly used functions is the FORMAT function, which allows you to change the format of a date value with the help of a custom format string.
The syntax of the FORMAT
function is as follows:
FORMAT(date, format)
The date
is the date value that you want to format, and the format
is the custom format string that specifies how the date should be displayed.
For example, let’s say you want to display the current date in the format of ‘yyyy-MM-dd’, you can use the following query:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS 'Current Date'
// 2023-01-12
In this query, the FORMAT
function is used to format the current date (GETDATE()
) using the format string ‘yyyy-MM-dd’. The resulting column is given the name ‘Current Date’.
Here are some other examples of how to use the FORMAT
function:
MS SQL Custom format a date in the format of ‘dd/MM/yyyy’
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS 'Current Date'
// 12/01/2023
MS SQL Custom format a date in the format of ‘MM/dd/yyyy’
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS 'Current Date'
// 01/12/2023
MS SQL Custom format a date in the format of ‘dddd, mmmm dd, yyyy’
SELECT FORMAT(GETDATE(), 'dddd, mmmm dd, yyyy') AS 'Current Date'
// Thursday, 11 12, 2023
You can also use the FORMAT
function in combination with other functions and clauses to format date values from a specific table. For example, to display the date of a specific order in the format ‘yyyy-MM-dd’ from the Orders table, you can use the following query:
SELECT FORMAT(OrderDate, 'yyyy-MM-dd') AS 'Order Date'
FROM Orders
WHERE OrderID = 123
In this query, the FORMAT
function is used to format the OrderDate
column from the Orders table using the format string ‘yyyy-MM-dd’. The query also uses a WHERE
clause to filter the results to show only the order with an OrderID
of 123.
The FORMAT
function can be also combined with other SQL Server functions like DATEADD
, DATEDIFF
, etc to manipulate and filter the date.
SELECT FORMAT(DATEADD(day, -30, GETDATE()), 'yyyy-MM-dd') AS 'Date 30 days ago'
// 2022-12-13
In this example, DATEADD
function is used to calculate the date 30 days ago and FORMAT
function is used to format the date.
Run the SQLFiddle link here to get the output and try some more formatting on your own.
In conclusion, the FORMAT
function is a powerful tool that allows you to format and display dates in a specific format in MS SQL Server. With the ability to use a custom format string, you can easily format your date values to meet your specific needs.
Top comments (0)