Solved

Posted on 2009-05-07
724 Views

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

My problem is when joining values at the end of the month.

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
Question by:gdemaria
• 6
• 4
• 3
• +2

LVL 75

Expert Comment

0

Expert Comment

Just a quick question, does April 1st then match with May 1st as well?
0

LVL 39

Author Comment

@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

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

March 30 = April 30
March 31 = May 1

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

LVL 39

Author Comment

>  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

@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 59

Expert Comment

Try this:
``````-- for testing purposes

DECLARE @date datetime

SET @date = '2009-03-31'

-- end testing portion

``````
0

LVL 75

Expert Comment

0

LVL 59

Expert Comment

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

Kevin Cross earned 500 total points
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;
``````
0

LVL 59

Expert Comment

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

@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

0

LVL 39

Author Comment

0

LVL 39

Author Comment

Thanks very much!

Here is my completed function.

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
``````
0

## Featured Post

### Suggested Solutions

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.