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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: