SQL Server Date Functions

Introduction

Date and Time plays major role in many projects. SQL Server has several date and time functions. In this article, we will discuss all the Date and Time functions in SQL Server with an Example.

CURRENT_TIMESTAMP()

Returns the date and time of the machine the SQL Server is running on.

Example

SELECT CURRENT_TIMESTAMP AS [Current Date & Time];

DATEADD()

Adds a time or date interval to a date and then returns the date.

Example

SELECT DATEADD(YEAR, 2, '2021/03/25') AS [Year Added];

SELECT DATEADD(MONTH, 2, '2021/03/25') AS [Month Added];

DATEDIFF()

Returns the difference between two dates.

Example

SELECT DATEDIFF(YEAR, '2020/04/25', '2023/10/25') AS [Year Difference];

SELECT DATEDIFF(MONTH, '2021/08/25', '2023/08/25') AS [Month Difference];

SELECT DATEDIFF(HOUR, '2020/08/25 07:00', '2020/08/25 12:45') AS [Hour Difference];

DATEFROMPARTS()

Returns a date from the specified parts. Arguments values order are Year, Month and Day.

Example

SELECT DATEFROMPARTS(2021, 05, 31) AS [Date];

DATENAME()

Returns a specified part of a date as string.

Example

SELECT DATENAME(YEAR, '2021/03/25') AS [Year];

SELECT DATENAME(MONTH, '2021/03/25') AS [Month]; 

SELECT DATENAME(HOUR, '2021/03/25 18:54') AS [Hour];

SELECT DATENAME(MINUTE, '2021/03/25 08:36') AS [Minute];

DATEPART()

Returns a specified part of a date as integer.

Example

SELECT DATEPART(YEAR, '2021/08/25') AS [Year]; 

SELECT DATEPART(MONTH, '2021/08/25') AS [Month]; 

DAY()

Returns the day of the month for a specified date.

Example

SELECT DAY('2021/08/25') AS [Day of Month];

GETDATE()

Returns the current database system date and time.

Example

SELECT GETDATE() AS [Date];

GETUTCDATE()

Returns the current database system UTC date and time.

SELECT GETUTCDATE() AS [UTC Date];

ISDATE()

Checks an expression and returns 1 if it is a valid date, otherwise 0.

Example

SELECT ISDATE('2021-08-25');

SELECT ISDATE('Sundaram');

MONTH()

Returns the month part for a specified date

SELECT MONTH('2017/08/25') AS [Month];

SYSDATETIME()

Returns the date and time of the computer where the SQL Server is running.

SELECT SYSDATETIME() AS [System Date Time];

YEAR()

Returns the year part for a specified date.

SELECT YEAR('2021/08/25') AS [Year];

Conclusion

In this article we have discussed the Date and Time functions in SQL Server. I hope you all found this article much useful. Please share your feedback in the comment section.

Consider reading other SQL articles of Mine

• SQL Server String Functions
• Aggregate Functions in SQL Server
• Retrieve Unsaved SQL Query Scripts
• Select Statement Execution Order In SQL Server
• System Databases in SQL Server
• MS SQL Server – Zero to Hero Query Master – Part 4
• Schema Comparisons using Visual Studio SQL Data Tools
• Link a SQL Server Database Project to a Git Repository
• Create and Publish SQL Server Database Project With Visual Studio
• Customize Azure Data Studio with Dashboard Widgets
• Cycle Clipboard Ring In SSMS – Reuse Copied Items
• Set custom colors to differentiate between environments in SSMS
• Recover unsaved SQL queries in SSMS
• Multiple Backup Files of the SQL Server database with SSMS and T-SQL
• Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server