Solved

adding a month to a date using dateAdd()

Posted on 2009-05-07
15
737 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
[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
  • 6
  • 4
  • 3
  • +2
15 Comments
 
LVL 75

Expert Comment

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

Expert Comment

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

Author Comment

by:gdemaria
ID: 24330153
@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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 35

Expert Comment

by:YZlat
ID: 24330211
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
ID: 24330251

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
ID: 24330289
>  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
ID: 24330314
@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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330373
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
ID: 24330377
DATEADD(d,DATEDIFF(d,DATEADD(m,datediff(m,'',MarketDate),''), DATEADD(m,datediff(m,'',dateadd(m,1,MarketDate) ) ,'')  ), MarketDate)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330413
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 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24330441
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 60

Expert Comment

by:Kevin Cross
ID: 24330446
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
ID: 24330449

@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
ID: 24330469
@mwvisa1, you're ahead of me :)     Working on your latest posts...
0
 
LVL 39

Author Comment

by:gdemaria
ID: 24330826
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a 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.

635 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