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:

## 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.

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

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

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

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

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

Thanks sir

Thanks for the overview, I appreciate the time you put into this.

Thank your for your help ! nice article.

thank you sir for great tutorial

Hi Sankar, I’m glad you like the tutorial. Keep coming back!

Kris.

thank you for this article it really help

You’re welcome!

Hello all,

Could someone please help me make sense of the below, and what this is trying to achieve?:

IF DATEpart(“weekday”,NOW()) < [Select Day for Latest Week Switchover]

THEN DATE(DATEADD('day',(DATEpart("weekday",NOW())+7)*-1,NOW()))

ELSE

DATE(DATEADD('day',DATEpart("weekday",NOW())*-1,NOW()))

END

Any help would be much appreciated.

how to add criteria to get data just for last 2 weeks?

You can use the DATEDIFF function…

For example this expression will filder for AccountDates that are from either THIS week or two weeks prior…

DATEDIFF(w, GetDate(), AccountDate) between -2 and 0

Another way would be to calculate days…

DATEDIFF(d, GetDate(), AccountDate) between -14 and 0

filters for AccountDate between TODAY and 14 days ago.

How can I filter results to only include records month to date or year to date?

How do i prompt a user to enter a start date and end date on a query

i would like to use SyDate time in mmmy query i have the following query. can you help.

SELECT

LIVE.STOCK.LOC_0 AS Location,

LIVE.STOCK.LOCTYP_0 As ‘Location Type’,

LIVE.STOCK.ITMREF_0 AS ITEM_SKU,

LIVE.ITMMASTER.ITMDES1_0 As Description,

LIVE.STOCK.LOT_0 As LOT,

LIVE.STOCK.SLO_0 AS SUB_LOT,

FORMAT(LIVE.STOCK.QTYSTU_0,’N2′) As Quanity_Active

FROM

LIVE.ITMMASTER

LEFT JOIN

LIVE.STOCK

ON LIVE.ITMMASTER.ITMREF_0 = LIVE.STOCK.ITMREF_0

WHERE

(

((LIVE.STOCK.LOCTYP_0) = ‘9000’)

)

OR

(

((LIVE.STOCK.LOCTYP_0) = ‘9100’)

)