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