[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

DATEADD FUNCTION

Posted on 1998-09-16
1
Medium Priority
?
692 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
[X]
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
1 Comment
 
LVL 2

Accepted Solution

by:
formula earned 40 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

656 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