Home

Transact-SQL Built-In Functions
 

Date and Time Based Functions

 

Introduction

Date and time values are highly used in database applications. They involve sales, time sheets, taxes, overtime work, etc. Based on this usefulness, their operations are supported by various libraries you will be using when developing your application. Without being the most elaborate on this issue, Transact-SQL provides its own level of support for date and time values.

Before using a date or a time value in a calculation, remember that you must first get it one way or another. You can define a date or a time constant in your application. An example would be '1992/10/28'. You can declare a DateTime or a SmallDateTime variable and initialize it as you see fit. You may get a date or a time from another function. As the last alternative, you may get a date or time from another application or from a user. Once you have an appropriate date, you can use it.
 
 

The Current System Date and/or Time

One of the ways you can assist the user with date and time is to get the current date or the current time. For example, if you create a time sheet, when the user starts using it, it would be convenient to fill part of the time sheet with such predictable values.

To get the current date and the current time of the computer that a user is using, you can use the GETDATE() function of Transact-SQL. Its syntax is:

GETDATE()

This function simply returns the current date and time of the operating system.

Date/Time Addition

One of the primary operations you may want to perform on a date or a time value would consist of adding a value to it. To support this operation, Transact-SQL provides the DATEADD() function. Its syntax is:

DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)

The third argument to this function is the value of a date or a time on which the operation will be performed. It can be a constant value in the form of 'year/month/day' for a date or 'hour:minutes AM/PM' for a time.

The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06.

When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:

  • If you want to add a number of years to a date, specify the TypeOfValue as Year or yy, or yyyy (remember that SQL is case-insensitive). Here is an example:
      
    DECLARE @Anniversary As DateTime;
SET @Anniversary = '2002/10/02';
SELECT DATEADD(yy, 4, @Anniversary) AS Anniversary;
GO
    DATEADD
  • If you want to add a number of quarters of a year to a date, specify the TypeOfValue as Quarter or d, or qq. Here is an example:
      
    DECLARE @NextVacation As DateTime;
SET @NextVacation = '2002/10/02';
SELECT DATEADD(Quarter, 2, @NextVacation) AS [Next Vacation];
GO
    DATEADD 
  • If you want to add a number of months to a date, specify the TypeOfValue as Month or m, or mm. The following example adds 5 months to its date:
      
    DECLARE @SchoolStart As DateTime;
SET @SchoolStart = '2004/05/12';
SELECT DATEADD(m, 5, @SchoolStart) AS [School Start];
GO
    Adding Quarters to a Date

In the same way, you can add values as follows:

Type of Value Abbreviation As a result
Year yy A number of years will be added to the date value
yyyy
quarter q A number of quarters of a year will be added to the date value
qq
Month m A number of months will be added to the date value
mm
dayofyear y A number of days of a year will be added to the date value
dy
Day d A number of days will be added to the date value
dd
Week wk A number of weeks will be added to the date value
ww
Hour hh A number of hours will be added to the time value
minute n A number of minutes will be added to the time value
mi
second s A number of seconds will be added to the time value
ss
millisecond ms A number of milliseconds will be added to the time value
 

Date/Time Subtraction

Another regular operation performed on a date or a time value consists of getting the number of units that has elapsed in the range of two dates or two time values. To support this operation, Transact-SQL provides the DATEDIFF() function. Its syntax is:

DATEDIFF(TypeOfValue, StartDate, EndDate)

This function takes three arguments. The second argument is the starting date or the starting time of the range to be considered. The third argument is the end or last date or time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function:

Type of Value Abbreviation As a result
Year yy The function will return the number of years that have elapsed between the start and the end dates
yyyy
quarter q The function will return the number of quarters of a year that have elapsed between the start and the end dates
qq
Month m The function will return the number of months that have elapsed between the start and the end dates
mm
dayofyear y The function will return the number of days of a year that have elapsed between the start and the end dates
dy
Day d The function will return the number of days that have elapsed between the start and the end dates
dd
Week wk The function will return the number of weeks that have elapsed between the start and the end dates
ww
Hour hh The function will return the number of hours that have elapsed between the start and the end times or dates
minute n The function will return the number of minutes that have elapsed between the start and the end times or dates
mi
second s The function will return the number of seconds that have elapsed between the start and the end times or dates
ss
millisecond ms The function will return the number of milliseconds that have elapsed between the start and the end times or dates

Here is an example that calculates the number of years that an employees has been with the company:

DECLARE @DateHired As DateTime,;
	@CurrentDate As DateTime;
SET @DateHired = '1996/10/04';
SET @CurrentDate  = GETDATE();
SELECT DATEDIFF(year, @DateHired, @CurrentDate)
       AS [Current Experience];
GO

This would produce:

DateDiff
 

Published on Monday 24 December 2007

 
Home Copyright © 2007 FunctionX, Inc.