Learn to use SQL Date Functions

Learn to use SQL Date Functions

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

If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions.

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.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

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 in commonly used in business.

In the following tables I categorized the functions and color coded them.  The color code corresponds to the likely hood you would use that particular function in a business environment.  Green are 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:

DateTime Functons – Most Common are Colored Green

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 is 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 function such as DATENAME, DATEPART, DATEDIFF, and DATEADD.

Datepart Values

The names are pretty self-explanatory.  Each date part corresponds to a well know 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

DATENAME Results

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

DATENAME – Notice OrderWeek is sorted as a character value

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 then 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

DATEPART Results

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:

DATEPART Results Sorted in Numeric Order

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 function work in 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 in play

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

Whose results are:

DAY, MONTH, and YEAR results

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 returnes the number 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 product 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

DATEDIFF Results

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

Use DATEADD to add DAYS to a DATE