Learn to Use SQL Server Date Functions

SQL server’s date functions provide you a set of functions that you can use to manipulate dates. The functions are used for a wide variety of operations such as adding weeks to a date, calculating the difference between two dates, or to decompose a date into its fundamental parts.

To get the most of this and our other lessons, be sure to practice using the examples!

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.

Introduction to SQL Server’s Date Functions

There are over twenty five different functions categorized as date functions in SQL server.

All of the functions are listed on the Date Functions (Transact-SQL) page. I would recommend visiting that page to learn about each function.

Rather than reiterate that material, we’ll focus on the functions I’ve seen commonly used in business.

In the following tables, I categorized the functions and color coded them. The color code corresponds to the likelihood you would use that particular function in a business environment. Green is most likely to be used, and red less.

This isn’t a strict scale, and all functions have a use in some business case, but I wanted a way to help you winnow down the field to those most relevant.

Here is my attempt:

Functions Used to Get the Current Date and Time

Of all of the functions that are used to return the current date and time, the GETDATE function is the most commonly used function.

GETDATE

The GETDATE function is used to return the current data and time of the server. This function becomes handy when you need to compare the data such as the last payment date to the current date.

The general form of the GETDATE function is:

GETDATE()

Here is an example using GETDATE:

SELECT GETDATE()

Which returns 2015-08-09 21:55:00.477.

A more useful example is to use GETDATE with DATEDIFF or DATEADD. Check out DATEDIFF for a more comprehensive example.

Functions to Get Date Parts

Date parts refer to various components of a date such as the year, month, or day. The date parts are used in several functions in this section and others. Here is a table of date parts that you can use in functions such as DATENAME, DATEPART, DATEDIFF, and DATEADD.

The names are pretty self-explanatory. Each date part corresponds to a well known period such as the month or hour.

DATENAME

DATENAME is a very useful function that is used to return various parts of a date such as the name of the month, or day of the week corresponding to a particular date.

The general form of DATENAME is:

DATENAME(date part, value)

Where date part correspond to a list of predefined parts and the value is the date you are working with.

This function returns a character value.

Here is an example which shows various date parts in action.

SELECT DATENAME(year, GETDATE()) as Year,
      DATENAME(week, GETDATE()) as Week,
      DATENAME(dayofyear, GETDATE()) as DayOfYear,
      DATENAME(month, GETDATE()) as Month,
      DATENAME(day, GETDATE()) as Day,
      DATENAME(weekday, GETDATE()) as WEEKDAY

Assuming GETDATE returns 2015-08-10 12:56:25.313, the example query results are:

Note: There are many other date parts you can use with DATENAME. You can find a complete list on the MSDN site.

Here is an example showing how you can use DATANAME to perform some summary level analysis of orders.

Suppose the Sales Manager would like a summary of weekly sales. Using the DATENAME function, we can provide that information.

SELECT  DATENAME(year, OrderDate) as OrderYear,
        DATENAME(week, OrderDate) as OrderWeek,
        SUM(TotalDue) as WeeklySales
FROM    Sales.SalesOrderHeader
WHERE   DATENAME(year, OrderDate) = '2008'
GROUP BY DATENAME(year, OrderDate), DATENAME(week, OrderDate)
ORDER BY DATENAME(year, OrderDate), DATENAME(week, OrderDate)

Here are the results:

I want to point out that though we sorted by year and week, the weeks seem to be out of order. This is because the OrderWeek returned is a character value. In order to sort them numerically, we need to convert the result to a numeric value.

We could easily convert the value within the ORDER BY clause, but that isn’t necessary once you learn about DATEPART.

DATEPART

Returns an integer which represents the specified part of a date. It works very similar to DATENAME, in that you specify a date part and value, however, rather than returning a text value, such as ‘July’, it returns 7.

The general form of DATEPART is:

DATEPART(date part, value)

Where date part corresponds to a list of predefined parts and the value is the date you are working with.

This function returns an integer.

Here is an example which shows various date parts in action.

SELECT DATEPART(YEAR, GETDATE()) as Year,
      DATEPART(WEEK, GETDATE()) as Week,
      DATEPART(DAYOFYEAR, GETDATE()) as DayOfYear,
      DATEPART(MONTH, GETDATE()) as Month,
      DATEPART(DAY, GETDATE()) as Day,
      DATEPART(WEEKDAY, GETDATE()) as WEEKDAY

Assuming GETDATE returns 2015-08-10 12:56:25.313, the example query results are:

Note: There are many other date parts you can use with DATEPART. You can find a complete list on the MSDN site.

We can also get a summary of sales totals using DATEPART as we did for DATENAME. Here is the query:

SELECT  DATEPART(year, OrderDate) as OrderYear,
        DATEPART(week, OrderDate) as OrderWeek,
        SUM(TotalDue) as WeeklySales
FROM    Sales.SalesOrderHeader
WHERE   DATEPART(year, OrderDate) = 2008
GROUP BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)
ORDER BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)

You notice that these results are sorted correctly according to week:

DAY, MONTH, YEAR

The DAY, MONTH, and YEAR functions return, based on a date provided, the day, month, or year as an integer.

The general form is the same for all three functions. For brevity, we’ll just show DAY, the other two functions work in a similar fashion.

Here is its general form:

DAY(value)

Where value is the date you which to work with.

Here is a quick example showing these functions at play:

SELECT HireDate,
      YEAR(HireDate) as HireYear,
      MONTH(HireDate) HireMonth,
       DAY(HireDate) as HireDay
FROM  HumanResources.Employee

Whose results are:

You may have noticed that YEAR(value) is short hand DATEPART(year, value). Both return the same result, here is a SQL statement you can try:

SELECT HireDate,
      YEAR(HireDate) as HireYear,
      DATEPART(year, HireDate) as HireYearFromDatePart
FROM  HumanResources.Employee

You’ll find similar equivalences for MONTH and DAY.

Modifying and Finding Difference in Dates

DATEDIFF

The DATEDIFF function returns the number of years, months, weeks, or days between two dates.

The general form for the DATEDIFF is:

DATEDIFF(date part, start value, end value)

Where date part corresponds to a list of predefined date parts such as year, month, weeks, and days.

The difference is calculated between start value and end value.

This function may seem a bit complicated, but it is really worth understanding as it has many practical uses in SQL.

You can use it to calculate the number of days a bill is past due, or the number of days or weeks between two dates.

Consider this example:

The HR Vice President wants to award all employees with a years of service award. She would like you to produce a report of all employees with more than ten years of service.

To do this, we’ll use the DATEDIFF report to both display number of years of service and to also to filter out those with less than ten years of service.

Here is the SQL:

SELECT  NationalIDNumber,
        HireDate,
         DATEDIFF(year, HireDate, GETDATE()) YearsOfService
FROM    HumanResources.Employee
WHERE   DATEDIFF(year, HireDate, GETDATE()) >= 10
ORDER BY YearsOfService DESC

Whose results are:

DATEADD

The DATEADD function is used to add years, months, weeks, or days between to a date.

The general form for the DATEADD is:

DATEADD(date part, number, value)

Where date part corresponds to a list of predefined date parts such as year, month, weeks, and days and number specifies the number of date parts to add to value.

If number is positive, then that number of date parts are added to the value.

If number is negative, then effective the date parts are subtracted from value.

This function may seem a bit complicated, but it is really worth understanding as it has many practical uses in SQL.

You can use it to calculate the number of days until bill is past due, or the number of days or weeks into the future or past.

Consider this example:

The sales manager has been talking to the production manager about selling items on the web. They want to produce the bikes as they are ordered. If bikes were ordered today, when could be they be available to ship to the customer?

If you look at the Product table, you’ll see there is a DaysToManufacture field. Using this with DATEADD in conjunction with GETDATE(), we can calculate the number of days into the future.

SELECT NAME,
       DaysToManufacture,
       GETDATE() as Today,
       DATEADD(day, DaysToManufacture, GETDATE()) EarliestDate
FROM   Production.Product
WHERE  DaysToManufacture > 1

The results for this are: