T-SQL Tutorial

T-SQL Date functions


This article describes how to use the T-SQL Date functions in SQL Server database. There are several SQL Server Date functions. To help you improve the efficiency of your SQL Server performance, you will find some useful ideas for functions here.

It is a common practice in SQL to use date functions to display a certain date, as well as to extract information such as hour, day, month, year. Also, with the help of date functions, time intervals can be added to a date.

We will discuss the following data type functions in SQL Server: GETDATE, DATEPART, DATEADD, DATEDIFF, DATENAME, CURRENT_TIMESTAMP, EOMONTH, ISDATE, SYSDATETIME.


GETDATE

The GETDATE function will return the datetime value, which consists of both the date and time at the moment where the SQL Server runs on your computer. The time mentioned in the output will be purely displayed in milliseconds.

SELECT GETDATE() as CurrentDatetime;
SELECT CONVERT (date, GETDATE()) as System_date;


DATEPART

The DATEPART function will return an integer value consisting of the datepart of the specific date on which it is executed. The datepart in the syntax can be mentioned as follows:
Year - yy,yyyy
Quarter - qq, q
Month - mm, m
Dayofyear - dy, y
Day - dd, d
Week- wk,ww
Weekday - dw,w
Hour - hh
Minute - mi,n
Second - ss,s
Millisecond – mcs
Nanosecond – ns Nanosecond

SELECT DATEPART(day, getdate()) as CurrentDay;
SELECT DATEPART(month, getdate()) as CurrentMonth;
SELECT DATEPART(year, getdate()) as CurrentYear;


DATEADD

The DATEADD function will return the new datetime value. The return data type is the data type of the date value.

SELECT DATEADD(day, 7, getdate()) as DueDate;
SELECT DATEADD(month, 3, getdate()) as DueMonth;


DATEDIFF

The DATEDIFF function will return the difference between the date or time across the two mentioned dates. The return type of this function is an integer value.

SELECT DATEDIFF(hour, '2022-07-23', '2022-07-25') as HourDifference;
SELECT DATEDIFF(day, '2022-07-23', '2022-07-28') as DayDifference;
SELECT DATEDIFF(month, '2022-07-23', '2022-09-25') as MonthDifference;





DATENAME

The DATENAME function returns the specified name of the date. The result is returned as a string value by this function.

SELECT DATENAME(year,'2022-07-29 18:22:35') as Year;
SELECT DATENAME(month,'2022-07-29 18:22:35') as Month;
SELECT DATENAME(day,'2022-07-29 18:22:35') as Day;


CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time.

SELECT CURRENT_TIMESTAMP;
SELECT CONVERT(char(25), CURRENT_TIMESTAMP);


EOMONTH

Based on the date that is given as an input parameter, the EOMONTH function figures out the last date of the month.

SELECT EOMONTH ( GETDATE() ) as EndOfCurrentMonth;
SELECT EOMONTH ( GETDATE(), 1 ) as EndOfNextMonth;
SELECT EOMONTH('2022-02-15') as EndOfFebMonth;


ISDATE

The ISDATE function checks an expression and returns 1 if it is a valid date, otherwise 0.

SELECT ISDATE('07/29/2022') as Valid_date;
SELECT ISDATE('15/15/2022') as Invalid_date;


SYSDATETIME

The SYSDATETIME function returns the date and time of the computer where the SQL Server is running. SYSDATETIME is a built-in SQL function that returns the current system date and time, i.e. DATETIME2, on which the SQL Server instance is now running, using the Date Function. The accuracy of the SYSDATETIME() function is better than that of GETDATE() in terms of fractional seconds.

SELECT SYSDATETIME();
SELECT CONVERT(DATE, SYSDATETIME());
SELECT CONVERT(TIME, SYSDATETIME());