Calculate Month Diffrence

Question

Post reply

Calculate Month Diffrence

CrazyMan

SSChampion

Points: 12560
More actions
February 3, 2009 at 5:50 am

#128443

Hi All
I got a table with date time field, there can be any date on it , i need to choose records from the table that is greater than 1 month old
Create table #Month ( DateCreated Datetime)
Insert into #Month Values('2009-01-10 23:48:56.024')
Insert into #Month Values('2009-01-03 23:58:53.024')
Insert into #Month Values('2009-01-02 23:44:16.024')
Insert into #Month Values('2009-01-03 23:48:36.024')
Insert into #Month Values('2008-01-03 23:48:36.024')
Insert into #Month Values('2008-01-02 23:44:16.024')
I need result the result as 2009-01-02 23:44:16.024 and 2008-01-02 23:44:16.024,
Its like get a month's old record from today, despite when ever its been created
Thanks in advance 🙂
Cheers
🙂

Viewing 15 posts - 1 through 14 (of 14 total)

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

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 1

I'm not sure I've understand what you want here.

If you want all records greater than a month old, why isn't 2008-01-03 23:48:36.024 in your result set?

Derek

CrazyMan SSChampion Points: 12560 More actions · Answer 2

Hi Derek, thanks for the reply.

Because its exactly a month as of today, i am looking for records whose datecreated is more than a month. 🙂

Cheers

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 3

Isn't the difference of the dates that you supplied a year... a bit confusing??

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Hi Visal

Thanks, i need all records that more than a months old despite of year, ( say a user created a record 2 months back on 1st) i need the record on select,

The condition is this, i need to send all the users a monthly mail from their join date, this has to be dispatched each month on the day when they join in on the table. 🙂

Hope this explains my situation 🙂

JestersGrind SSCertifiable Points: 5812 More actions · Answer 5

It sounds like you need to evaluate the individual date parts instead of the whole date. Something like this might work.

SELECT *

FROM #Month

WHERE DATEPART(dd, DateCreated) = DATEPART(dd, GetDate()) AND DATEPART(mm, DateCreated) = DATEPART(mm, GetDate()) - 1

The above returns records where the day number match and the month number is one less.

Hope this helps,

Greg

CrazyMan SSChampion Points: 12560 More actions · Answer 6

Thanks G2, but this only gives me a record for last month, but i need users who are created on previous month as well, 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

CrazyMan (2/3/2009)
Hi Visal
Thanks, i need all records that more than a months old despite of year, ( say a user created a record 2 months back on 1st) i need the record on select,
The condition is this, i need to send all the users a monthly mail from their join date, this has to be dispatched each month on the day when they join in on the table. 🙂
Hope this explains my situation 🙂

It helps everybody if you take the time to ensure that the question, and any sample data, is correct. I think this is what you are looking for.

SET DATEFORMAT YMD

DROP TABLE #Month

Create table #Month ( DateCreated Datetime)

Insert into #Month Values('2009-01-02 23:48:56.024')

Insert into #Month Values('2009-01-03 23:58:53.024')

Insert into #Month Values('2009-01-04 23:44:16.024')

Insert into #Month Values('2009-01-05 23:48:36.024')

Insert into #Month Values('2008-01-02 23:48:36.024')

Insert into #Month Values('2008-01-03 23:44:16.024')

Insert into #Month Values('2008-01-04 23:44:16.024')

SELECT *,

DATEPART(dd, DateCreated) AS DayOfMonthCreated,

DATEDIFF(mm, DateCreated, GETDATE()) AS MonthsLag,

CASE WHEN DATEDIFF(mm, DateCreated, GETDATE()) > 1 THEN 1

WHEN DATEDIFF(mm, DateCreated, GETDATE()) = 1

AND DATEPART(dd, DateCreated) < DATEPART(dd, GETDATE()) THEN 1 -- either this

--AND DATEPART(dd, DateCreated) <= DATEPART(dd, GETDATE()) THEN 1 -- or this

ELSE 0 END AS [1 month old]

FROM #Month

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JestersGrind SSCertifiable Points: 5812 More actions · Answer 8

I reread your post above. Sounds like a rather strange requirement. I'm not sure why you wouldn't just want everything that is a month older. That would be three records in your given data. Anyway, this might work except that I don't think that you will get any records on the first of the month.

SELECT *

FROM #Month

WHERE DATEPART(mm, DateCreated) < DATEPART(mm, GetDate()) AND

DATEPART(dd, DateCreated) < DATEPART(dd, GetDate())

CrazyMan SSChampion Points: 12560 More actions · Answer 9

Thanks Chris

I got a route to go from this, thanks, i will have to modify some conditions to suit my needs, thanks a lot for every one 🙂

CrazyMan SSChampion Points: 12560 More actions · Answer 10

Hi All

This is what result i was expecting, Once again thanks for All your post

SET DATEFORMAT YMD
DROP TABLE #Month
Create table #Month ( DateCreated Datetime)
Insert into #Month Values('2009-01-02 23:48:56.024')
Insert into #Month Values('2009-01-03 23:58:53.024')
Insert into #Month Values('2009-01-04 23:44:16.024')
Insert into #Month Values('2009-01-05 23:48:36.024')
Insert into #Month Values('2008-01-02 23:48:36.024')
Insert into #Month Values('2008-01-03 23:44:16.024')
Insert into #Month Values('2008-01-04 23:44:16.024')
SELECT *,
DATEPART(dd, DateCreated) AS DayOfMonthCreated,
DATEDIFF(mm, DateCreated, GETDATE()) AS MonthsLag,
CASE WHEN DATEDIFF(mm, DateCreated, GETDATE()) = 1
AND DATEPART (dd,DateCreated)=DATEPART(dd,getdate()-1) THEN 1
WHEN DATEDIFF(mm, DateCreated, GETDATE()) > 1
AND DATEPART(dd, DateCreated) = DATEPART(dd, GETDATE()-1) THEN 1
ELSE 0 END AS [1 month old]
FROM #Month

Thanks Once Again 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Test with some extra data, CrazyMan. It doesn't work.

Insert into #Month Values('2008-12-30 23:48:56.024')

Insert into #Month Values('2008-12-31 23:48:56.024')

Insert into #Month Values('2009-01-01 23:48:56.024')

Insert into #Month Values('2009-01-02 23:48:56.024')

Insert into #Month Values('2009-01-03 23:58:53.024')

Insert into #Month Values('2009-01-04 23:44:16.024')

Insert into #Month Values('2009-01-05 23:48:36.024')

Insert into #Month Values('2008-01-02 23:48:36.024')

Insert into #Month Values('2008-01-03 23:44:16.024')

Insert into #Month Values('2008-01-04 23:44:16.024')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

CrazyMan SSChampion Points: 12560 More actions · Answer 12

Hi Chris

It works, it returns all rows with date (2nd) despite of what ever month (expect current month), sorry if i haven't got the wordings right:-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

CrazyMan (2/3/2009)
Hi Chris
It works, it returns all rows with date (2nd) despite of what ever month (expect current month), sorry if i haven't got the wordings right:-)

No problem Crazyman, I understand there's a language difference here.

Will public holidays and weekends not cause problems?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

CrazyMan SSChampion Points: 12560 More actions · Answer 14

Hi Chris

No it wont cause any problems, Its like a monthly subscription mail ( i should have used this term earlier 😉 ), that goes out each month for users who have registered and are a month old( i mean 30 days after the mail has been sent)

Cheers

🙂