problem in partial month - SQL Server Forums

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

SQL Server Development (2000)

problem in partial month

Author

Topic

abhi143
Starting Member

32 Posts

Posted - 2006-01-18 : 01:20:49

hi..

iam working in stored procedure in SQL server,

i have to write a query in stored procedure for the partial month calculation...

i have two field, in which user enter date..

first field = 01/01/2004(dd/mm/yyyy)
second field = 04/01/2004(dd/mm/yyyy)

it will give me 3 month difference,

but now when i increase day of second field to 04/02/2004..then also it will give me 3 month differnce,

what i want to do, in such a case it will increase one month, if the day will increase one day itself.

so simply i want month difference as per that...

01/01/2004 - 04/02/2004 ---4 months(i want this result)

please help me

thanks

Abhi

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-18 : 01:28:09

Use
Set dateformat dmy
select datediff(mm,'01/01/2004','04/01/2004')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 02:06:01

quote:
first field = 01/01/2004(dd/mm/yyyy)
second field = 04/01/2004(dd/mm/yyyy)

It should be mm/dd/yyyy right ? Try to use YYYYMMDD notation to avoid confusion

declare
	@dt1	datetime,
	@dt2	datetime

select	@dt1	= '2004/01/01',
	@dt2 	= '2004/04/02'

select 	datediff(month, @dt1, @dt2) as diff_mths,
	datediff(month, @dt1, @dt2) +
	ceiling(datediff(day, dateadd(month, datediff(month, @dt1, @dt2), @dt1), @dt2) / 31.0)
	as diff_add_partial

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 02:24:21