adding a month to a date using dateAdd()


I am joining two tables with dates.   From one table, I am getting the record whose date is one month behind the other date (March 1, 2008 joins with April 1, 2008).  I am using this statement...

sub.BeginDate = dateAdd(m, -1, ma.marketDate)


My problem is when joining values at the end of the month.
Using the dateAdd()  function

   March 31 will join with April 30

Since April does not have 30 days, it gets April 30th instead.

Is there a way to match the NEXT day instead of the Previous day?

   I need March 31st to join to May 1st,  not April 30th

Thanks!!


LVL 39
gdemariaAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Here is the SQL code you could use in the function:
DECLARE @date datetime, @date_calc datetime
SET @date = '2009-01-31'
SET @date_calc = DATEADD(month, 1, DATEDIFF(day, 0, @date))
 
IF DAY(@date_calc) <> DAY(@date)
SET @date_calc = DATEADD(day, 1, @date_calc)
 
SELECT @date, @date_calc;

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sub.BeginDate = dateAdd(m, -1, ma.marketDate)+1
0
 
mariecCommented:
Just a quick question, does April 1st then match with May 1st as well?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gdemariaAuthor Commented:
@anneeshattingal,

When the date is March 1st, that will match April 2nd.. which is incorrect

The goal is...

March 1 = April 1
March 2 = April 2
...
March 30 = April 30
March 31 = May 1
etc..



0
 
YZlatCommented:
why don't you use first day of the month instead?

SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' +
                CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01' AS DATETIME)

will return May 1st

The code below will put March 1st into BeginDate and April 1st into EndDate:


sub.BeginDate =CAST(CAST(YEAR(DateAdd(m,-2,GETDATE())) AS VARCHAR(4)) + '/' +
                CAST(MONTH(DateAdd(m,-2,GETDATE()))  AS VARCHAR(2)) + '/01' AS DATETIME)

sub.EndDate =CAST(CAST(YEAR(DateAdd(m,-1,GETDATE()))  AS VARCHAR(4)) + '/' +
                CAST(MONTH(DateAdd(m,-1,GETDATE())) AS VARCHAR(2)) + '/01' AS DATETIME)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

March 30 = April 30
March 31 = May 1

and
What for  April 1   = ?????????  is it may 1 or May2  ?
0
 
gdemariaAuthor Commented:
>  What for  April 1   = ?????????  is it may 1 or May2  

it would be May 1.  

The goal is to match the same day, except when there is not matching date, it would go to the NEXT day instead of the previous day as dateAdd is doing

March 1 = April 1
March 2 = April 2
...
March 30 = April 30
March 31 = May 1
April 1 = May 1
April 2 = May 2
..
April 30 = May 30
May  1 = June 1
May 2 = June 2
etc..
0
 
gdemariaAuthor Commented:
@YZlat, it looks like your code always generates a 1st day of the month value.  I need to handle every day of the month, please see previous post for match-ups.
0
 
Kevin CrossChief Technology OfficerCommented:
Try this:
-- for testing purposes
DECLARE @date datetime
SET @date = '2009-03-31'
-- end testing portion
 
SELECT DATEADD(day, DAY(@date)-1, DATEADD(month, 1, DATEDIFF(day, 0, @date-DAY(@date)+1)))

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
DATEADD(d,DATEDIFF(d,DATEADD(m,datediff(m,'',MarketDate),''), DATEADD(m,datediff(m,'',dateadd(m,1,MarketDate) ) ,'')  ), MarketDate)
0
 
Kevin CrossChief Technology OfficerCommented:
This puts the result the same number of days into the next month as you are currently in this month; therefore, the following is also true as well as what you have above:

January 31 = March 03 (since February only has 28 days)
February 28 = March 28

Hopefully this is inline with what you want.
The other results worked out correct.

If you need it exactly, then you need to create a user defined function that does the dateadd then compare if the two DAY() values of both dates equal.  If not, then add a day.
0
 
Kevin CrossChief Technology OfficerCommented:
Using this:

January 31 = March 01
February 28 = March 28
March 31 = May 01
May 01 = June 01
May 02 = June 02
etc...
0
 
gdemariaAuthor Commented:

@mwvisa1

That's really close, thanks!!

The problem I found was with January, because February is extra short, a Jan 30th or Jan 31st date do not match March 1.

Sorry - it just got a little harder !!
Jan 28 = Feb 28
Jan 29 = Mar 1
Jan 30 = Mar 1
Jan 31 = Mar 1


-- for testing purposes
DECLARE @date datetime
SET @date = '2009-01-30'
-- end testing portion
 
SELECT DATEADD(day, DAY(@date)-1, DATEADD(month, 1, DATEDIFF(day, 0, @date-DAY(@date)+1)))
0
 
gdemariaAuthor Commented:
@mwvisa1, you're ahead of me :)     Working on your latest posts...
0
 
gdemariaAuthor Commented:
Thanks very much!

Here is my completed function.

I added a couple parameters:  
   one to see if I wanted the day before or the day after the given date
   and one to see if I am going back a month, year, quarter

Works awesome, thanks!

alter FUNCTION dbo.previousDate ( @theDate datetime, @isDayBefore bit, @thePeriod varchar(20) )
  RETURNS dateTime
AS 
BEGIN
DECLARE @result dateTime;
 
 
  IF @thePeriod = 'month'   SET @result = DATEADD(month, -1, @theDate)
  IF @thePeriod = 'quarter' SET @result = DATEADD(quarter, -1, @theDate)
  IF @thePeriod = 'year'    SET @result = DATEADD(year, -1, @theDate)
 
  IF @isDayBefore = 1 and DAY(@result) <> DAY(@theDate)
     SET @result = DATEADD(day, 1, @result)
         
  RETURN @result;
END -- end function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.