DateAdd Function on 30th and 31st of any month

Question

Post reply

DateAdd Function on 30th and 31st of any month

babuganesh2000

Hall of Fame

Points: 3123
More actions
November 27, 2006 at 2:24 pm

#117816

Hi Everyone,
When I run the dateadd function adding months on 30th and 31st of any
months returns the same result. I am OK with that because logically it
is correct.
If I have to do a reverse lookup for the date, how can I do that.
Here is the code sample...
select dateadd(mm,6,'2006-05-30 00:00:00.000')
select dateadd(mm,6,'2006-05-31 00:00:00.000')
-- 2006-11-30 00:00:00.000
-- 2006-11-30 00:00:00.000
select dateadd(mm,-6,'2006-11-30 00:00:00.000')
select dateadd(mm,-6,'2006-12-01 00:00:00.000')
--2006-05-30 00:00:00.000
--2006-06-01 00:00:00.000
The problem I have is when I do a reverse lookup I am missing all the
records which got created on
2006-05-31 00:00:00.000.
Thanks in Advance....
Thanks,
Ganesh

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Maybe you can change the way you approach the problem. What is the business requirement?

Sergiy SSC Guru Points: 110208 More actions · Answer 2

You don't need 30th ot 31st date. You need LAST date of the month.

Last day of the month is ALWAYS the one day before the 1st of the next month.

1st is 1st for every month.

Have you got it or I need to continue?

_____________
Code for TallyGenerator

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 3

Thanks for the reply

The requirement is like this. I have a table

Policy_Term

policyid policy_term_id valid_start_date valid_end_date

X123 1 05/31/2006 NULL

x123 2 11/30/2006 NULL

This is how the data looks, the same table has the valid_bit column. I have to update the valid_bit to 0 for the previous term.

It's basically a insurance firm, all policies gets renewed every 6 months.

Please assist

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 4

Sergiy was on it. The way you want to do it is move to the first of the start month, then add seven months, then back up a single day.

That will give you the last day of the month six months from the start date.

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 5

Example script below... there's probably a cleaner way to do this but this is what I just threw together:

DECLARE @dateDATETIME

SET @date = GETDATE()-- or whatever date

-- 1 - DATEPART(dd, @date) will give you the first of the month.

SELECT DATEADD(dd, 1 - DATEPART(dd, @date), @date)

-- Then move forward 7 months.

SELECT DATEADD(mm, 7, DATEADD(dd, 1 - DATEPART(dd, @date), @date))

-- Finally subtract a single day to find the last day of the month, 6 months from the start.

SELECT DATEADD(dd, -1, DATEADD(mm, 7, DATEADD(dd, 1 - DATEPART(dd, @date), @date)))

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 6

Thanks to everyone....

I hope this looks very easy to me....

If the date is last day of the month {

SELECT dateadd(DAY, -1, dateadd(MONTH, -6, dateadd(DAY, 1, @date)))

}

else{

SELECT dateadd(MONTH,-6,@date)

}

Please correct me If I am missing something

Thanks,

Ganesh

Sergiy SSC Guru Points: 110208 More actions · Answer 7

You are probably missing the case of February.

You better use :

IF DAY(@date) <> DAY(dateadd(MONTH,-6,@date))

....

_____________
Code for TallyGenerator

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 8

Yep. Thanks for the input.....

Finally I got this.....Could you please take a look and let me know if I am missing other than this....

declare @inputdate datetime

set @inputdate = '11/30/06 0:00'

if datediff (mm,@inputdate, @inputdate + 1 ) = 1 and datepart(day,@inputdate) in (30,29,28)

select dateadd(day, -1, dateadd(month, -6, dateadd(day, 1, @inputdate)))

else if datediff (mm,@inputdate, @inputdate + 1 ) = 1 and datepart(day,@inputdate) = 31

select dateadd(day, -1, dateadd(month, -6, dateadd(day, -1, @inputdate)))

else

select dateadd(mm,-6,@inputdate)

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Try it for

set @inputdate = '12/31/06 00:00'

_____________
Code for TallyGenerator

Jeff Block Old Hand Points: 334 More actions · Answer 10

try calculating the first day of the month following and the subtract 1 minute, you'll have the last day of the month irregardless if it is 30, 31 , 29, or 28.

try this:

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

(for the last day of the current month but you can substitute for getdtae())

Todd Payne SSC-Addicted Points: 468 More actions · Answer 11

All this date math is fun but wouldn't it be easier to update the valid bit for all

records that have the same policyID and a Start Date that is less than 11/30/2006.

My guess is that only one policy should be active for the same ploicyID.

Do we need to know the start date of the old policy or just that it is older than the current one?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Please note: 5 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass