Solved

adding a month to a date using dateAdd()

Posted on 2009-05-07
15
727 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
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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

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 59

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 59

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 59

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

21 Experts available now in Live!

Get 1:1 Help Now