SQL Server provides several functions for formatting and manipulating datetime values. These functions allow you to convert datetime values to different string formats, extract individual parts of a datetime value, and perform other operations. In this article, we will explore some of the most commonly used SQL Server datetime functions and show examples of how to use them.
CONVERT()
The CONVERT() function is one of the most versatile functions for formatting datetime values in SQL Server. It allows you to convert a datetime value to a specific style or format. The basic syntax for the CONVERT() function is as follows:
CONVERT(data_type, expression, style)
where data_type is the data type to which you want to convert the expression, expression is the datetime value that you want to convert, and style is an optional parameter that specifies the format of the output.
For example, the following query converts a datetime value to the format "yyyy-mm-dd hh:mi:ss":
SELECT CONVERT(NVARCHAR(30), GETDATE(), 120) AS 'Formatted Date Time';
The output will be in format '2022-09-20 21:23:45'
Another example :
SELECT CONVERT(NVARCHAR(30), GETDATE(), 101) AS 'Formatted Date Time';
The output will be in format '09/20/2022'
FORMAT()
The FORMAT() function is another function that you can use to format datetime values in SQL Server. It was introduced in SQL Server 2012, and it allows you to format a datetime value using a format string. The basic syntax for the FORMAT() function is as follows:
FORMAT(expression, format)
where expression is the datetime value that you want to format, and format is a string that specifies the format of the output.
For example, the following query formats a datetime value as "dd-mm-yyyy":
SELECT FORMAT(GETDATE(), 'dd-MM-yyyy') AS 'Formatted Date Time';
The output will be in format '20-09-2022'
DATEPART()
The DATEPART() function allows you to extract the year, month, day, hour, minute, or second from a datetime value. The basic syntax for the DATEPART() function is as follows:
DATEPART(datepart, date)
where datepart is the part of the datetime value that you want to extract, and date is the datetime value from which you want to extract the part.
For example, the following query extracts the year from a datetime value:
SELECT DATEPART(YEAR, GETDATE()) AS 'Year';
The output will be '2022'
DATEADD()
The DATEADD() function allows you to add or subtract a specified amount of time from a datetime value. The basic syntax for the DATEADD() function is as follows:
DATEADD(datepart, number, date)
where datepart is the part of the datetime value that you want to add or subtract, number is the number of units of the datepart to add or subtract, and date is the datetime value to which you want to add or subtract the time.
For example, the following query adds 3 months to a datetime value:
SELECT DATE
DATENAME()
The DATENAME() function allows you to extract the name of the specified part of a datetime value. The basic syntax for the DATENAME() function is as follows:
DATENAME(datepart, date)
where datepart is the part of the datetime value for which you want to extract the name, and date is the datetime value from which you want to extract the name.
For example, the following query extracts the name of the month from a datetime value:
SELECT DATENAME(MONTH, GETDATE()) AS 'Month Name';
The output will be 'September'
DATEDIFF()
The DATEDIFF() function allows you to calculate the difference between two datetime values. The basic syntax for the DATEDIFF() function is as follows:
DATEDIFF(datepart, startdate, enddate)
where datepart is the part of the datetime value that you want to use to calculate the difference, startdate is the starting datetime value, and enddate is the ending datetime value.
For example, the following query calculates the number of days between two datetime values:
SELECT DATEDIFF(DAY, '2022-08-01', GETDATE()) AS 'Days';
It will return number of days between '2022-08-01' and current date
GETDATE()
The GETDATE() function returns the current date and time of the system. The basic syntax for the GETDATE() function is as follows:
GETDATE()
For example, the following query returns the current date and time of the system:
SELECT GETDATE() AS 'Current Date Time';
It will return current date time of the system.
GETUTCDATE()
The GETUTCDATE() function returns the current date and time of the system in Universal Coordinated Time (UTC) format. The basic syntax for the GETUTCDATE() function is as follows:
GETUTCDATE()
For example, the following query returns the current date and time of the system in UTC format:
SELECT GETUTCDATE() AS 'UTC Date Time';
It will return current date time of the system in UTC format.
In conclusion, SQL Server provides a wide range of functions for formatting and manipulating datetime values. These functions allow you to convert datetime values to different string formats, extract individual parts of a datetime value, add or subtract time, and perform other operations. By using these functions, you can easily format datetime values according to your requirements and perform various types of date and time calculations.
## Popular questions
1. What function can be used to convert a datetime value to a specific style or format in SQL Server?
- The CONVERT() function can be used to convert a datetime value to a specific style or format in SQL Server.
2. What function can be used to format a datetime value using a format string in SQL Server?
- The FORMAT() function can be used to format a datetime value using a format string in SQL Server.
3. What function can be used to extract the year, month, day, hour, minute, or second from a datetime value in SQL Server?
- The DATEPART() function can be used to extract the year, month, day, hour, minute, or second from a datetime value in SQL Server.
4. What function can be used to add or subtract a specified amount of time from a datetime value in SQL Server?
- The DATEADD() function can be used to add or subtract a specified amount of time from a datetime value in SQL Server.
5. What function can be used to get current date time of the system in SQL Server?
- The GETDATE() function can be used to get current date time of the system in SQL Server.
### Tag
Datetime.
