Welcome, Dev! As a developer, you know the importance of managing dates and times in your application. SQL Server provides various functions to format dates and times to meet your specific needs. In this article, we will cover everything you need to know about SQL Server Format Date. Let’s dive in!
What is a Date Format in SQL Server?
A date format in SQL Server is a specification that determines the layout of a date or time value. It helps to display the date and time in a readable format while storing it in the database. By default, SQL Server stores the date and time in a 24-hour format with a precision of three milliseconds. However, it allows you to format the date and time as per your requirement using various built-in functions.
How to Display Date and Time in SQL Server?
To display the current date and time in SQL Server, you can use the GETDATE() function. It returns the current system date and time in the default format of ‘YYYY-MM-DD HH:MI:SS:MMM’. For example:
Function |
Output |
|---|---|
SELECT GETDATE() |
2022-01-01 11:05:43:000 |
You can also use the CONVERT() function to format the date and time as per your requirement. For example:
Function |
Output |
|---|---|
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) |
01/01/2022 |
In the above example, we have used the CONVERT() function to format the date in ‘DD/MM/YYYY’ format. We will discuss more such functions in the upcoming sections.
SQL Server Date and Time Data Types
Before we dive into the date and time functions, let’s understand the different data types used to store date and time values in SQL Server.
Date Data Type
The date data type in SQL Server stores only the date component without any time information. It uses four bytes of storage and can represent dates between January 1, 1753, and December 31, 9999.
Time Data Type
The time data type in SQL Server stores only the time component without any date information. It uses five bytes of storage and can represent times between 00:00:00.0000000 and 23:59:59.9999999.
Datetime Data Type
The datetime data type in SQL Server stores both date and time components. It uses eight bytes of storage and can represent dates between January 1, 1753, and December 31, 9999, with a precision of three milliseconds.
Datetime2 Data Type
The datetime2 data type in SQL Server stores both date and time components with more precision than the datetime data type. It uses six to eight bytes of storage, depending on the precision, and can represent dates between January 1, 0001, and December 31, 9999, with a precision of 100 nanoseconds.
SmallDatetime Data Type
The smalldatetime data type in SQL Server stores both date and time components with less precision than the datetime data type. It uses four bytes of storage and can represent dates between January 1, 1900, and June 6, 2079, with a precision of one minute.
SQL Server Date and Time Functions
SQL Server provides various built-in functions to format dates and times. Let’s explore some of the most commonly used functions.
1. CONVERT()
The CONVERT() function in SQL Server is used to convert an expression from one data type to another. It takes three arguments: the target data type, the expression to be converted, and the style.
The style argument determines the output format of the date and time. It can be a predefined format or a user-defined format in the form of a string. The most commonly used styles are:
Style |
Description |
|---|---|
101 |
mm/dd/yyyy |
102 |
yyyy.mm.dd |
103 |
dd/mm/yyyy |
104 |
dd.mm.yyyy |
105 |
dd-mm-yyyy |
For example, to convert a datetime value to a date value, you can use the following query:
Function |
Output |
|---|---|
SELECT CONVERT(DATE, ‘2022-01-01 11:05:43:000’) |
2022-01-01 |
2. FORMAT()
The FORMAT() function in SQL Server is used to format a value based on a specified format string. It takes two arguments: the expression to be formatted and the format string.
The format string can contain placeholders for various date and time elements, such as year, month, day, hour, minute, second, and millisecond. For example, to format a datetime value as ‘January 01, 2022’, you can use the following query:
Function |
Output |
|---|---|
SELECT FORMAT(‘2022-01-01 11:05:43:000’, ‘MMMM dd, yyyy’) |
January 01, 2022 |
3. DATEPART()
The DATEPART() function in SQL Server is used to extract a specific part of a date or time value. It takes two arguments: the date or time part to be extracted and the expression to be evaluated.
The date or time part can be year, quarter, month, day, hour, minute, second, millisecond, or any other valid date or time part. For example, to extract the year from a datetime value, you can use the following query:
Function |
Output |
|---|---|
SELECT DATEPART(YEAR, ‘2022-01-01 11:05:43:000’) |
2022 |
4. DATEADD()
The DATEADD() function in SQL Server is used to add or subtract a specified interval of time from a date or time value. It takes three arguments: the date or time part to be added or subtracted, the number of intervals, and the expression to be evaluated.
The date or time part can be year, quarter, month, day, hour, minute, second, millisecond, or any other valid date or time part. The number of intervals can be positive or negative, depending on whether you want to add or subtract time. For example, to add one month to a datetime value, you can use the following query:
Function |
Output |
|---|---|
SELECT DATEADD(MONTH, 1, ‘2022-01-01 11:05:43:000’) |
2022-02-01 11:05:43:000 |
5. DATEDIFF()
The DATEDIFF() function in SQL Server is used to calculate the difference between two date or time values in a specified date or time part. It takes three arguments: the date or time part, the start date or time, and the end date or time.
The date or time part can be year, quarter, month, day, hour, minute, second, millisecond, or any other valid date or time part. The start and end dates or times can be any valid datetime values. For example, to calculate the number of days between two datetime values, you can use the following query:
Function |
Output |
|---|---|
SELECT DATEDIFF(DAY, ‘2022-01-01 11:05:43:000’, ‘2022-01-15 11:05:43:000’) |
14 |
FAQ
1. How do I convert a string to a datetime in SQL Server?
To convert a string to a datetime in SQL Server, you can use the CONVERT() function with a suitable style. For example, to convert the string ‘2022-01-01’ to a datetime value, you can use the following query:
Function |
Output |
|---|---|
SELECT CONVERT(DATETIME, ‘2022-01-01’) |
2022-01-01 00:00:00.000 |
2. How do I get the current date and time in SQL Server?
To get the current date and time in SQL Server, you can use the GETDATE() function. For example, to get the current date and time in ‘YYYY-MM-DD HH:MI:SS:MMM’ format, you can use the following query:
Function |
Output |
|---|---|
SELECT GETDATE() |
2022-01-01 11:05:43:000 |
3. How do I extract the month from a datetime value in SQL Server?
To extract the month from a datetime value in SQL Server, you can use the DATEPART() function with the month part. For example, to extract the month from the datetime ‘2022-01-01 11:05:43:000’, you can use the following query:
Function |
Output |
|---|---|
SELECT DATEPART(MONTH, ‘2022-01-01 11:05:43:000’) |
1 |
4. How do I add one month to a datetime value in SQL Server?
To add one month to a datetime value in SQL Server, you can use the DATEADD() function with the month part and an interval of 1. For example, to add one month to the datetime ‘2022-01-01 11:05:43:000’, you can use the following query:
Function |
Output |
|---|---|
SELECT DATEADD(MONTH, 1, ‘2022-01-01 11:05:43:000’) |
2022-02-01 11:05:43:000 |
5. How do I format a datetime value in SQL Server?
To format a datetime value in SQL Server, you can use the CONVERT() or FORMAT() function with a suitable style or format string. For example, to format the datetime ‘2022-01-01 11:05:43:000’ as ‘January 01, 2022’, you can use the following queries:
Function |
Output |
|---|---|
SELECT CONVERT(VARCHAR(20), ‘2022-01-01 11:05:43:000’, 107) |
January 01, 2022 |
SELECT FORMAT(‘2022-01-01 11:05:43:000’, ‘MMMM dd, yyyy’) |
January 01, 2022 |
Conclusion
In this article, we have covered everything you need to know about SQL Server Format Date. We have discussed the date and time data types, the commonly used date and time functions, and the frequently asked questions. We hope this article helps you to manage dates and times in your application effectively. Happy coding, Dev!