Date function in SQL Server

SQL Server provides a variety of date and time functions that can be used to manipulate and format date and time values. Here are some examples of commonly used date functions in SQL Server:

• GETDATE (): This function returns the current date and time of the system in which the SQL Server instance is running. For Example, SELECT GETDATE () AS CurrentDateTime;

This query returns the current date and time in the format YYYY-MM-DD hh:mm: ss.mmm. For example, the result might look like this:

CurrentDateTime

2023-11-04 16:37:32.000

You can use the GETDATE () function in various scenarios, such as when you need to record the current date and time of an event, or when you need to calculate the difference between two dates.

• DATEADD (): This function adds or subtracts a specified time interval from a date or time value. For example, DATEADD (day, 7, ‘2023-11-04’) adds 7 days to the specified date.

SELECT DATEADD (day, 7, GETDATE ()) AS NextWeek;

This query adds 7 days to the current date and time and returns the result in the format YYYY-MM-DD hh:mm: ss.mmm. For example, if the current date and time is 2023-11-04 16:37:32.000, the result would be 2023-11-11 16:37:32.000.

You can use other time intervals such as year, month, hour, minute, second, etc. with DATEADD (). 

• DATEDIFF (): This function returns the difference between two date or time values in a specified time interval. For example, DATEDIFF (day, ‘2023-11-04’, ‘2023-11-08’) returns 4, which is the number of days between the two dates.
• CONVERT (): This function converts a date or time value to a specified data type. For example, CONVERT (varchar, GETDATE (), 101) returns the current date in the format MM/DD/YYYY.