Solved

adding a month to a date using dateAdd()

Posted on 2009-05-07
15
724 Views
Last Modified: 2012-05-06

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!!


0
Comment
Question by:gdemaria
  • 6
  • 4
  • 3
  • +2
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
sub.BeginDate = dateAdd(m, -1, ma.marketDate)+1
0
 

Expert Comment

by:mariec
Comment Utility
Just a quick question, does April 1st then match with May 1st as well?
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
@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
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility

March 30 = April 30
March 31 = May 1

and
What for  April 1   = ?????????  is it may 1 or May2  ?
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
>  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
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
@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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
DATEADD(d,DATEDIFF(d,DATEADD(m,datediff(m,'',MarketDate),''), DATEADD(m,datediff(m,'',dateadd(m,1,MarketDate) ) ,'')  ), MarketDate)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Using this:

January 31 = March 01
February 28 = March 28
March 31 = May 01
May 01 = June 01
May 02 = June 02
etc...
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility

@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
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
@mwvisa1, you're ahead of me :)     Working on your latest posts...
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now