Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 3, 2003

Working with SQL Server Date/Time Variables: Part Four - Date Math and Universal Time

By Gregory A. Larsen

This article will be the last in my data/time series, and will discuss the last few date functions I have yet to cover in this series. I will discuss how to use the DATEDIFF and DATEADD functions to perform different date related mathematical calculations. I will also talk about what universal time is and discuss how the GETDATE and GETUTCDATE functions work.

If your application needs to take a date entered, or a date stored in the database and calculate a date in the future or the past, or compare two dates to determine the number of days between them, then the DATEADD and DATEDIFF functions can be used to perform these tasks.

DATEADD

The DATEADD function can be used to add or subtract a number of days, years, or some other time related datepart from a datetime value. Here is how to call the DATEADD function.

	DATEADD ( datepart , number, date ) 

Where the datepart parameter is one of the following: year, quarter, month, dayofyear, day, week, hour, minute, second or millisecond. The number parameter is an integer value for the number of dateparts to be added to or subtracted from the date parameter.

Now depending on your needs, your application might use this function to perform mathematical calculations on a given date. So let's come up with a couple of different possible date calculations that an application might use, to demonstrate how to use the DATEADD function.

For my first example, let's assume you have an application that produces invoices to be sent to customers. On the invoice you have two dates, an invoice date, and an invoice due date. The invoice date is the current date, and the invoice due date is calculated by adding 21 days to the invoice date. Below is how you would use the DATEADD function to calculate the invoice due date based on the current date (invoice date).

DECLARE @INVOICE_DATE DATETIME
DECLARE @DUE_DATE DATETIME
SET @INVOICE_DATE = GETDATE()
SET @DUE_DATE = DATEADD(DAY,21,@INVOICE_DATE)
PRINT 'INVOICE DATE: ' + CAST
    (@INVOICE_DATE AS CHAR(11)) + CHAR(13) +  
      'DUE DATE: ' + CAST (@DUE_DATE AS CHAR(11))

For the next example, say you have a car insurance type of application, where policies are renewed every 6 months. If you only store the policy date in your table then you can calculate the policy renewal date by using the DATEADD function. Let's say you have the following policy dates (policy_dt) for each policy (policy_id).

POLICY_ID

POLICY_DT

1

2002-10-30

2

2002-10-30

3

2002-06-06

4

2003-04-31

5

2002-05-21

If we use using the "quarter" datepart of the DATEADD function we can easily calculate the 6 month renewal date, by adding 2 "quarter" dateparts to the POLICY_DT column. Here is how we would display all the renewal dates for the above policies:

SELECT POLICY_ID, DATEADD(QUARTER,2,POLICY_DT) RENEWAL_DATE FROM CONTRACTS


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date