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
GETDATE, DATEPART, DATEADD, DATEDIFF, DATENAME, CURRENT_TIMESTAMP, EOMONTH, ISDATE, SYSDATETIME.
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 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
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 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 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 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 function returns the current date and time.
SELECT CONVERT(char(25), CURRENT_TIMESTAMP);
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 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 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 CONVERT(DATE, SYSDATETIME());
SELECT CONVERT(TIME, SYSDATETIME());