Date calculations

In SQL Server 2005 you can do date calculations in several ways. SQL Server 2005 has several functions you can use when you work with dates. This article describes a number of practical examples that you will run into when you work with databases and dates.

For example, it shows how to calculate back in time, how to calculate the difference in days between dates and how to get the month from a date.

DATEADD()

You use this function to find a date before or after a given date. For example, you want to know the date 2 months after today, or the date two days before 1 August.

Syntax DateAdd(datepart, number, date)Examples

The date of the day after tomorrow

1
SELECT DATEADD(dd, 2, GETDATE () )

In the example above we tell it to calculate in days (dd). The 2 tells it to count 2 days ahead. The third argument is the date to start from, in this case today (GETDATE() ). Instead of GETDATE() you can also pass a date, for example ‘2008-12-05’ . Note that it must be in quotes!

The date of two months ago

1
SELECT DATEADD(mm, -2, GETDATE () )

mm stands for months. -2 tells it to go back 2 months, and GETDATE() gives today’s date.

DATEPART()

With this function you can get the day, month, year and time from a date value. Say you want the month part of the date 2008-12-05, in this case 12. You may also need the day of 12 August 2007, which is 12.

Syntax
DatePart(datepart, date)

Examples

The day of today’s date

1
SELECT DATEPART(dd,GETDATE() )

The year of today’s date

1
SELECT DATEPART(yy,GETDATE() )

DATEDIFF()

You use Datediff to calculate an interval between two dates. That way you can find the number of days between two dates.

Syntax

DateDiff(datepart, date1, date2)

Examples

The number of days between 1 December 2008 and today (5 December 2008)

1
SELECT DATEDIFF(dd,'2008-12-01',GETDATE() )

DATENAME()

Datename is a lot like datepart. The difference is that this function returns a string (a piece of text), while datepart returns a number. This function can return the day of the week as text, or the name of the month.

Syntax

DateName(datepart, date)ExamplesThe name of today’s weekday

1
SELECT DATENAME (dw, GETDATE() )

Today’s day number in the year

1
SELECT DATENAME (dy, GETDATE() )

Below is a table with values you can enter as the first argument:

One limitation of the function is that it returns the English names. So I recommend that you always use a date dimension. The big advantage of a date dimension is that for every date you know which week it falls in, which year, which quarter, etc. Keep in mind that 1 January can fall in week 52 of the previous year. In that case the year of 1 January is, for example, 2008, but the year of the week number is 2007.