none
datediff function for day only after complete month RRS feed

  • 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

Answers

  •         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 AJIT DALLA 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 AJIT DALLA 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 Acamar 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)
        if dateadd(month,@month,@datefiled1) >@datefiled2
        begin
        set @month=@month-1
        end
        set @day=DATEDIFF(day,dateadd(month,@month,@datefiled1),@datefiled2)
        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 AJIT DALLA 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 AJIT DALLA 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 AJIT DALLA Monday, November 20, 2017 10:03 AM
    Monday, November 20, 2017 10:03 AM