# datediff function for day only after complete month

• ### Question

• i have two filed of date, i want count total month and  day

i use

datediff("m" datefiled1,datefiled2)

i give me colplete month count prefect

when i use

datediff("d" datefiled1,datefiled2)

then i give me all day differance

but i want only count reaming day after complete month

like datedif function

datedif(datefiled1,datefiled2,"md")

Monday, November 20, 2017 4:56 AM

• ```        Dim day1 As New DateTime(2017, 10, 15)
Dim day2 As New DateTime(2017, 9, 1)
Dim daydifference = day1.DayOfYear - day2.DayOfYear```

Be aware this gives the difference in days. The duration is always arbitrary if you count for instance 24 hour to 00 hour as a day it is one more.

Success
Cor

• Marked as answer by Monday, November 20, 2017 10:02 AM
Monday, November 20, 2017 9:50 AM
• yes it help me

= day(Fields!Dateofvisit1.Value)-day(Fields!Dateofbirth.Value)

• Marked as answer by Monday, November 20, 2017 10:03 AM
Monday, November 20, 2017 10:03 AM

### All replies

• reaming day after complete month

total day count by datediff  =45 from 1 September to 15 October

i want show it  as 1 month and 15 day

Monday, November 20, 2017 7:55 AM
• total day count by datediff  =45 from 1 September to 15 October

i want show it  as 1 month and 15 day

If you know the end date then the days remaining after the whole month will be the day of the month.
https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.dateandtime.day(v=vs.110).aspx

The whole months are calculated form the DateDiff.

• Edited by Monday, November 20, 2017 7:59 AM sp
Monday, November 20, 2017 7:58 AM
• I hope you require this code in SQL server and I assume this was your requirement:

IF datefiled1='21-jun-2015',datefiled2='22-aug-2016' then you need "1 year 2 months 1 day ".

Please find the below function which we have used in our application:

Create function DateDiffInDateParts(@datefiled1 smalldatetime,@datefiled2 smalldatetime)
returns varchar(50)
as
begin
declare @date3 smalldatetime
Declare @month int,@year int,@day int
if @datefiled1>@datefiled2
begin
set @date3=@datefiled2
set @datefiled2=@datefiled1
set @datefiled1=@date3
end
SELECT @month=datediff (MONTH,@datefiled1,@datefiled2)
begin
set @month=@month-1
end
set @year=@month/12
set @month=@month % 12
return (case when @year=0 then '' when @year=1 then convert(varchar(50),@year ) + ' year ' when @year>1 then convert(varchar(50),@year ) + ' years ' end)
+ (case when @month=0 then '' when @month=1 then convert(varchar(50),@month ) + ' month ' when @month>1 then convert(varchar(50),@month ) + ' months ' end)
+ (case when @day=0 then '' when @day=1 then convert(varchar(50),@day ) + ' day ' when @day>1 then convert(varchar(50),@day ) + ' days ' end)
end

Execute it and you will get the desired result.

declare @a varchar(500)
exec @a=get_Exact_Date_diff '21-jun-2015','22-aug-2016'
select @a

Monday, November 20, 2017 8:04 AM
• actually i want use this function rdlc report vb2008

i only want count day after all month complete.

date from                      date to

10/02/2002                     25/05/2005

result  15 days only.  i want ignore all month and year

• Edited by Monday, November 20, 2017 8:52 AM
Monday, November 20, 2017 8:38 AM
• if dtdate1 start from 09/05/2017 then
Monday, November 20, 2017 9:13 AM
• is the function in excel

=DATEDIF(A1,A2,"md") is meaningless

Monday, November 20, 2017 9:29 AM
• ```        Dim day1 As New DateTime(2017, 10, 15)
Dim day2 As New DateTime(2017, 9, 1)
Dim daydifference = day1.DayOfYear - day2.DayOfYear```

Be aware this gives the difference in days. The duration is always arbitrary if you count for instance 24 hour to 00 hour as a day it is one more.

Success
Cor

• Marked as answer by Monday, November 20, 2017 10:02 AM
Monday, November 20, 2017 9:50 AM
• yes it help me

= day(Fields!Dateofvisit1.Value)-day(Fields!Dateofbirth.Value)

• Marked as answer by Monday, November 20, 2017 10:03 AM
Monday, November 20, 2017 10:03 AM