using datediff in getting exact month difference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using datediff in getting exact month difference

Author

Topic

quietfulness
Starting Member

4 Posts

Posted - 2004-12-22 : 21:53:04

Good day.

I want to get how many months have passed from a date with another date.

For instance, datediff('2001-11-20','2001-12-22') returns 1.
However, datediff('2001-11-20',2001-12-01') returns 1, why.. It should return 0 coz 1 month will be on the 20th..

I want to resolve this coz im creating a vb.net program that updates user data 1 month after subscription..

How can i solve this..?

Any help would be greatly appreciated.
Thank you very much..

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 22:32:24

datediff returns the number of boundaries that are crossed so in this case as the month boundary is crossed it returns 1.

Use the subscription datetime and
if getdate() > dateadd(mm,1,subsdate)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-28 : 00:20:35

Say for instance that you want to get the month interval of the starting date to the current date? You could do the staff like this...

MonthInterval = CurrentDateTime - StartingDateTime

select datepart(mm,getdate()) - datepart(mm,'2004-11-20 12:28:41:093')

This will return month interval.

OKay mel? :D

Want Philippines to become 1st World COuntry? Go for World War 3...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-28 : 03:20:57

quote:

For instance, datediff('2001-11-20','2001-12-22') returns 1.
However, datediff('2001-11-20',2001-12-01') returns 1, why.. It should return 0 coz 1 month will be on the 20th..

hmm... weird that the sql statement even worked since datediff requires 3 parameters?

instead of month use day as interval then divide by number of days in a month to get month count based on days

select datediff(day,@d1,@d2)/@Days

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2004-12-28 : 13:15:52

"instead of month use day as interval then divide by number of days in a month to get month count based on days"

How many days in a month?

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-29 : 01:27:17

a. 28
b. 29
c. 30
d. 31

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2004-12-29 : 01:49:38

YuvarajKrishna
Starting Member

4 Posts

Posted - 2012-08-02 : 02:13:22

Declare @FrmDate Date = '02/01/2012' ---MM/DD/YYYY
Declare @EndDate Date = '03/01/2012' ---MM/DD/YYYY
SELECT DATEDIFF(DD,@FrmDate,@EndDate) /case DATEPART(MM,@FrmDate)
WHEN 1 Then 31
----Checking condition for leap year----
WHEN 2 THEN CASE DATEPART(YY,@FrmDate) % 4
WHEN 0 THEN 29
ELSE 28
END
WHEN 3 THEN 31 WHEN 4 THEN 30 WHEN 5 THEN 31 WHEN 6 THEN 30 WHEN 7 THEN 31 WHEN 8 THEN 31 WHEN 9 THEN 30 WHEN 10 THEN 31 WHEN 11 THEN 30 WHEN 12 THEN 31
END AS MONTHS

Yuvaraj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-03 : 01:58:48

See http://www.sqlteam.com/article/datediff-function-demystified

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources