Formatting Date in SQL Server

Greetings Dev! If you are a developer working with SQL Server, you must have come across a situation where you need to format dates to your desired format. This article aims to help you understand various date formats and how to format dates in SQL Server.

Date Formats in SQL Server

SQL Server uses the datetime or smalldatetime data type to store date and time values. These data types can represent a wide range of dates from January 1, 1753, to December 31, 9999, and can be formatted in various ways. Here are some of the common date formats:

Format	Example	Description
dd/mm/yyyy	01/01/2022	Date in day/month/year format
mm/dd/yyyy	01/01/2022	Date in month/day/year format
yyyy-mm-dd	2022-01-01	Date in year-month-day format

dd/mm/yyyy

The dd/mm/yyyy format is commonly used in many parts of the world including Europe, Asia, and Australia. In this format, the day is represented by two digits, followed by the month in two digits and then the year in four digits. To format a date in this format, you can use the CONVERT function with the style code of 103.

For example:

SELECT CONVERT(varchar, GETDATE(), 103) AS FormattedDate;

The output will be in the format of “dd/mm/yyyy”.

If you want to format a specific date value, you can replace the GETDATE() function with the date value.

mm/dd/yyyy

The mm/dd/yyyy format is commonly used in the United States and a few other countries. In this format, the month is represented by two digits, followed by the day in two digits and then the year in four digits. To format a date in this format, you can use the CONVERT function with the style code of 101.

For example:

SELECT CONVERT(varchar, GETDATE(), 101) AS FormattedDate;

The output will be in the format of “mm/dd/yyyy”.

yyyy-mm-dd

The yyyy-mm-dd format is an international standard and commonly used in many web-based applications. In this format, the year is represented by four digits, followed by the month in two digits and then the day in two digits. To format a date in this format, you can use the CONVERT function with the style code of 120.

For example:

SELECT CONVERT(varchar, GETDATE(), 120) AS FormattedDate;

The output will be in the format of “yyyy-mm-dd”.

Formatting Dates using FORMAT Function

Starting from SQL Server 2012, a new function called FORMAT is introduced to format dates and other data types. The FORMAT function is similar to the CONVERT function, but it provides more flexibility in formatting data.

You can use the FORMAT function in the following way to format dates:

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS FormattedDate;

The output will be in the format of “dd/MM/yyyy”.

The first argument of the FORMAT function specifies the value to format, and the second argument specifies the format string.

Frequently Asked Questions

What is the difference between datetime and smalldatetime?

The datetime data type stores dates and times with an accuracy of 3.33 milliseconds, whereas the smalldatetime data type stores dates and times with an accuracy of 1 minute.

How do I get the current date in SQL Server?

You can use the GETDATE() function to get the current date and time.

SELECT GETDATE() AS CurrentDate;

How do I add a number of days to a date in SQL Server?

You can use the DATEADD function to add a number of days to a date.

SELECT DATEADD(day, 10, GETDATE()) AS NewDate;

The above example adds 10 days to the current date.

How do I subtract a number of hours from a date in SQL Server?

You can use the DATEADD function with a negative value to subtract a number of hours from a date.

SELECT DATEADD(hour, -2, GETDATE()) AS NewDate;

The above example subtracts 2 hours from the current date.

How do I convert a string to a date in SQL Server?

You can use the CONVERT function to convert a string to a date by specifying the style code that matches the format of the string.

SELECT CONVERT(datetime, '2022-01-01') AS NewDate;

The above example converts the string ‘2022-01-01’ to a datetime value.

Conclusion

In this article, we have discussed various date formats and how to format dates in SQL Server using the CONVERT and FORMAT functions. We have also covered some frequently asked questions related to date manipulation in SQL Server. We hope this article has helped you in your development work.