Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DATEADD FUNCTION

Posted on 1998-09-16
1
679 Views
Last Modified: 2010-03-19
My previous applications runs in access2.0 wherein there is an built-in function "DateSerial"
which has an syntax like : DateSerial(year,month,day)
In my Sql Server I have an function lllr to this called "DateAdd".
Syntax : DateAdd(year,period,getdate())

In my case the month and day fields are 2 different fields which has smallint as datatype.
I have to add some integer values to month and day fields and concatenate with year field. The final result should return me the date in dd/mm/yyyy format.

For Eg. Year - 1998
           Month - 08 (Smallint Datatype)
           Day    - 16  (Smallint Datatype)
       
Add 7 to month field and 42 to Day field.
Taking today's date as "16/08/1998"
The final output should be    "27/03/1999"

I need the entire stuff of sql code ..

Can anyone help me out in this regard.

0
Comment
Question by:Favourites
1 Comment
 
LVL 2

Accepted Solution

by:
formula earned 20 total points
ID: 1090127
Here's your answer, which I tested in a SQL window:

/*         For Eg. Year - 1998
           Month - 08 (Smallint Datatype)
           Day    - 16  (Smallint Datatype)  */

declare @initialdate char(12)
declare @day smallint
declare @month smallint
declare @year smallint
declare @finaldate datetime

select @day=16
select @month=8
select @year=1998

select @initialdate = convert(char(2),@month) + '/' + convert(char(2),@day) + '/' + convert(char(4),@year)

select @finaldate=dateadd(month,7,(dateadd(day,42,@initialdate)))

select finaldate=convert(char(12),@finaldate,101)

Note that the returned date is exactly 1 month greater that you wanted it to be.  That's because you added 7 months and 42 days which is equivalent to 8 months plus 12 days, hence the extra month.  If this is not what you want, add 6 months and 42 days.
I also assumed year was also smallint in a separate field.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question