SQL Server DATEDIFF() Function

The simplest of the date/time functions is one that returns the current date and time. In Microsoft SQL Server, the function is named GETDATE. This function has no arguments. It merely returns the current date and time. For example:

SELECT GETDATE () 

Brings back an expression with the current date and time. Since the GETDATE function has no arguments, there is nothing between the parentheses. Remember, that a date/time field is a special data type that contains both a date and a time in a single field. An example of such a value is:

 2009-07-15 08:48:30

This value refers to the 15th of July 2009, at 48 minutes and 30 seconds past 8 a.m.

The next date/time function enables you to analyze any specified date and return a value to represent such elements as the day or week of the date. Again, the name of this function differs, depending on the database. In Microsoft SQL Server, this function is called DATEPART. The general format of this function is:

DATEPART (DatePart, DateValue)

The DateValue argument is any date. The DatePart argument can have many different values. Some examples of valid values are year, quarter, month, day of year, day, week, weekday, hour, minute, and second.

The following chart shows how the DATEPART function evaluates the date ‘7/2/2009’, with different values for the DatePart argument:

DATEPART Function Expression          Resulting Value

DATEPART (month, ‘7/2/2009’)                      7

DATEPART (day, ‘7/2/2009’)                          2

DATEPART (week, ‘7/2/2009’)                        27

DATEPART (weekday, ‘7/2/2009’)                   5

Looking at the values in the above chart, you can see that the month of 7/2/2009 is 7. The day is 2. The week is 27, because 7/2/2009 is in the 27th week of the year. The weekday is 5 because 7/2/2009 falls on a Thursday, which is the fifth day of the week.

In MySQL, the DATEPART function is named DATE_FORMAT, and it utilizes different values for the

DateValue argument. For example, to return the day of the date ‘7/2/2009’, you would issue this

SELECT in MySQL: 
SELECT DATE_FORMAT ('2009-07-02', '%d'); 

Oracle doesn’t have a function comparable to DATEPART.

The final date/time function enables you to determine the number of days (or weeks, months, etc.) between any two dates. Again, the name of this function differs, depending on the database. In Microsoft SQL Server, this function is called DATEDIFF, and the general format is:

DATEDIFF (DatePart, StartDate, EndDate) 

Valid values for the DatePart argument for this function include year, quarter, month, dayofyear, day, month, hour, minute, and second.

Here’s a chart that shows how the DATEDIFF function evaluates the difference between the dates 7/8/2009 and 8/14/2009, with different values for the DatePart argument:

DATEDIFF Function Expression                       Resulting Value

DATEDIFF (day, ‘7/8/2009’, ‘8/14/2009’)                       37

DATEDIFF (week, ‘7/8/2009’, ‘8/14/2009’)                     5

DATEDIFF (month, ‘7/8/2009’, ‘8/14/2009’)                   1

DATEDIFF (year, ‘7/8/2009’, ‘8/14/2009’)                      0

The above chart indicates that there are 37 days between the two dates. There are 5 weeks, 1 month, and 0 years between the dates.