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:
1 | 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:
1 2 3 4 | 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’
1 2 3 4 | 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’
1 2 3 4 | 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’
1 2 3 4 | 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:
1 2 3 4 5 | 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.
1 2 3 4 | 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.
