Date and Time Functions in SQL Server
In Date and Time Functions in SQL Server, we will learn about day, month, year, getdate, current _timestamp, datepart, datediff, dateadd, datename and, isdate functions.
DAY()
DAY() function return the day part (1 to 31) from a specified date. The return value will be integer.
SELECT DAY('2021-11-10') AS DayFunction;
Output

MONTH()
MONTH() function return the month part (1 to 12) from a specified date. The return value will be integer.
SELECT MONTH('2021-11-10') AS MonthFunction;
Output

YEAR()
YEAR() function return the year part from a specified date. The return value will be integer.
SELECT YEAR('2021-11-10') AS YearFunction;
Output

GETDATE()
GETDATE() function return the date and time of Operating System where SQL Server instance is installed.
SELECT GETDATE() AS fnGetDateTime;
Output

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP function return the date and time of Operating System where SQL Server instance is installed. It is very similar to GETDATE() function.
SELECT CURRENT_TIMESTAMP AS fnCurrentTimeStamp;
Output

DATEPART()
DATEPART() function is used to return day, month, year, hours, minutes, seconds, and nanoseconds etc. from the date. We get the result in integer type.
DATEEPART() function takes two parameters. First parameter is the datepart(day, month, year etc.) and the second parameter is the actual date.
Some of the DATEPART() function examples are listed below.
Syntax:
SELECT DATEPART(DATEPART,DATE);
Examples:
SELECT DATEPART(SECOND,'2021-11-13 12:15:55.277') AS SecondFromDatePart, DATEPART(MINUTE,'2021-11-13 12:15:55.277') AS MinuteFromDatePart, DATEPART(HOUR,'2021-11-13 12:15:55.277') AS HourFromDatePart, DATEPART(DAY,'2021-11-13 12:15:55.277') AS DayFromDatePart, DATEPART(WEEK,'2021-11-13 12:15:55.277') AS WeekFromDatePart, DATEPART(MONTH,'2021-11-13 12:15:55.277') AS MonthFromDatePart, DATEPART(YEAR,'2021-11-13 12:15:55.277') AS YearFromDatePart
Output

DATEADD()
DATEADD() function is used to add day, month, year, hours, minutes, seconds, and nanoseconds etc. in DATE and return the modified value.
This adds an integer value, and does not allow to add decimal value to date.
DATEADD() function takes three parameters. First parameter is the datepart(day, month, year etc.), Second parameter is the number which you want to add to date part and the third parameter is the actual date.
Syntax:
SELECT DATEADD(DATEPART,NUMBER,DATE);
Examples: In the following examples we will look how DATEADD function works.
- Adding two years in year datepart, and see the result in the below image.
SELECT DATEADD(YEAR,2,'2021-11-13') AS AddToYear;
Output

- Adding two months in month datepart, and see the result in the below image.
SELECT DATEADD(MONTH,2,'2021-11-13') AS AddToMonth;
Output

- Adding two Weeks will add week days in a month and show the result as below.
SELECT DATEADD(WEEK,2,'2021-11-13') AS AddWeeks;
Output

Similarly we can check with other parameters like hour, min, sec, miliisecond etc.
DDATEDIFF()
DATEDIFF() function return the count of day, month, year etc. in integer between two date values.
It takes three parameters. First parameter is the DATEPART(day, month, year etc.), Second parameter is the Starting Date, and the third parameter is the End Date.
Syntax:
SELECT DATEDIFF(DATEPART,STARTDATE,ENDDATE);
Examples: In the following examples we find the difference in years, months, weeks and days as shown below.
- Difference in years using DATEDIFF() function.
SELECT DATEDIFF(YEAR,'2018-01-01','2020-12-31') AS DiffInYears;
Output

- Difference in months using DATEDIFF() function.
SELECT DATEDIFF(MONTH,'2018-01-01','2020-12-31') AS DiffInMonths;
Output

- Difference in weeks using DATEDIFF() function.
SELECT DATEDIFF(WEEK,'2018-01-01','2020-12-31') AS DiffInWeeks;
Output

- Difference in days using DATEDIFF() function.
SELECT DATEDIFF(DAY,'2018-01-01','2020-12-31') AS DiffInDays;
Output

Similarly we can find the difference in minutes, seconds, milliseconds, nanoseconds etc. between two dates.
DATENAME()
DATENAME() function return the specified part(day, month, year etc.) of date in string.
This function takes two parameters. First parameter is the datepart(day, month, year etc.) of the date and the Second parameter is the actual date.
Syntax:
SELECT DATENAME(DATEPART,DATE);
Examples: In the following examples we will extract the year, month, week, and day from the date using DATENAME function.
- Extract year from the date using DATENAME function.
SELECT DATENAME(YEAR,'2021-12-30 16:50:30.577') AS YearUsingDateName;
Output

- Extract month from the date using DATENAME function.
SELECT DATENAME(MONTH,'2021-12-30 16:50:30.577') AS MonthUsingDateName;
Output

- Extract week from the date using DATENAME function.
SELECT DATENAME(WEEK,'2021-12-30 16:50:30.577') AS WeekUsingDateName;
Output

- Extract day from the date using DATENAME function.
SELECT DATENAME(DAY,'2021-12-30 16:50:30.577') AS DayUsingDateName;
Output

Similarly we can extract minutes, seconds, milliseconds, nanoseconds etc. from date using DATENAME function.
ISDATE()
ISDATE() function return 1 if the expression is a valid date else return 0 if there is invalid date.
SELECT ISDATE('2021-01-01') AS ValidDate; --return 1 SELECT ISDATE('ABC') AS InValidDate; --return 0
Recommended

How to use Transaction in SQL Stored Procedure

Difference Between IN and NOT IN Operators in SQL

How To Modify Date in SQL Using DATEADD

How To Get Year From Date in SQL Server

How To Get Month From Date in SQL Server

How To Get Day From Date in SQL Server

Advanced SQL Queries For Practice With Solution

SQL Queries For Practice With Solution
